Excel 의 데이터가 포함된 큰 스프레드시트를 가지고 있고 이 스프레드시트에서 특정 정보를 쉽게 필터링하고 추출할 수 있는 방법이 필요하십니까? Excel 에서 ^(Excel)VLOOKUP 을 사용하는 방법을 배운다면 하나의 강력한 Excel 기능^{(Excel function)} 으로 이 조회를 수행할 수 있습니다 .

Excel 의 VLOOKUP 함수^{(VLOOKUP function)} 는 매개 변수가 많고 사용 방법이 다양하기 때문에 많은 사람들에게 겁을 줍니다. 이 기사에서는 Excel 에서 ^(Excel)VLOOKUP 을 사용할 수 있는 모든 방법 과 이 기능이 강력한 이유에 대해 설명합니다.

Excel의 VLOOKUP 매개변수^{(VLOOKUP Parameters In Excel)}

Excel 의 아무 셀에나 =VLOOKUP( 을 입력하기 시작 하면 사용 가능한 모든 함수 매개변수를 보여주는 팝업이 표시됩니다.

각 매개변수와 그 의미를 살펴보겠습니다.

• lookup_value : 스프레드시트에서 찾고 있는 값
• table_array : 검색하려는 시트의 셀 범위
• col_index_num : 결과를 가져올 열
• [range_lookup] : 일치 모드^{(Match mode)} ( TRUE = approximate , FALSE = exact )

이 네 가지 매개변수를 사용하면 매우 큰 데이터 세트 내에서 데이터에 대해 다양하고 유용한 검색을 많이 수행할 수 있습니다.

간단한 VLOOKUP Excel 예제^{(A Simple VLOOKUP Excel Example)}

VLOOKUP^{(VLOOKUP isn)} 은 이미 배웠을 수도 있는 기본 Excel 기능 중 하나가 아니므로 시작하기 위해 간단한 예를 살펴보겠습니다.

다음 예에서는 미국 학교의 SAT 점수 스프레드 시트 ^(States)를 ^(United)사용^(SAT) 합니다 . 이 스프레드시트에는 읽기, 수학 및 쓰기에 대한  개별 SAT 점수 와 함께 450개 이상의 학교가 포함되어 있습니다 . 따라하기 위해 자유롭게^(Feel) 다운로드하십시오. 데이터를 가져오는 외부 연결이 있으므로 파일을 열 때 경고가 표시되지만 안전합니다.

관심 있는 학교를 찾기 위해 그렇게 큰 데이터 세트를 검색하는 것은 매우 시간이 많이 소요될 것입니다.

대신 표 측면의 빈 셀에 간단한 양식을 만들 수 있습니다. 이 검색을 수행하려면 School 에 대한 필드 하나를 만들고 읽기, 수학 및 쓰기 점수에 대한 추가 필드 3개를 만드십시오.

다음 으로 Excel 의 ^(Excel)VLOOKUP 함수^{(VLOOKUP function)} 를 사용하여 이 세 가지 필드가 작동하도록 해야 합니다. 읽기^(Reading) 필드에서 다음 과 같이 VLOOKUP 함수 를 만듭니다.^{(VLOOKUP function)}

1. 유형 =VLOOKUP(
2. 이 예에서 I2 인 ^(I2)School 필드^{(School field)} 를 선택합니다 . 쉼표를 입력합니다.
3. 조회할 데이터가 포함된 전체 셀 범위를 선택합니다. 쉼표를 입력합니다.

범위를 선택하면 조회하는 데 사용하는 열(이 경우 학교 이름 열^{(school name column)} )에서 시작한 다음 데이터가 포함된 다른 모든 열과 행을 선택할 수 있습니다.

참고^(Note) : Excel 의 ^(Excel)VLOOKUP 함수^{(VLOOKUP function)} 는 검색 열의^{(search column)} 오른쪽에 있는 셀만 검색할 수 있습니다 . 이 예에서 학교 이름 열^{(school name column)} 은 찾고 있는 데이터의 왼쪽에 있어야 합니다.

1. 다음으로, 읽기 점수^{(Reading score)} 를 검색 하려면 가장 왼쪽에 선택한 열에서 세 번째 열을 선택해야 합니다. 따라서 3 을 입력한 다음 다른 쉼표를 입력합니다.
2. 마지막으로 정확히 일치하려면 FALSE 를 입력하고 a ^(FALSE)) 로 함수를 닫습니다 .

최종 VLOOKUP 함수^{(VLOOKUP function)} 는 다음과 같아야 합니다.

=VLOOKUP(I2,B2:G461,3,FALSE)

처음 Enter 키를 누르고 기능^(Enter) 을 마치면 읽기 필드 에 ^{(Reading field)}#N/A 가 포함된 것을 알 수 있습니다 .

School 필드^{(School field)} 가 비어 있고 VLOOKUP 함수^{(VLOOKUP function)} 가 찾을 수 있는 항목 이 없기 때문 입니다. 그러나 조회하려는 고등학교 이름을 입력하면 읽기 점수^{(Reading score)} 에 대한 해당 행의 올바른 결과를 볼 수 있습니다.

대소문자를 구분하는 VLOOKUP을 처리하는 방법^{(How To Deal With VLOOKUP Being Case- Sensitive)}

데이터 세트에 나열된 것과 동일한 대소문자로 학교 이름을 입력하지 않으면 결과가 표시되지 않는다는 것을 알 수 있습니다.

VLOOKUP 함수^{(VLOOKUP function)} 는 대소문자를 구분 하기 때문 입니다. 이것은 특히 검색 중인 열이 대문자 사용 방식과 일치하지 않는 매우 큰 데이터 세트의 경우 성가실 수 있습니다.

이 문제를 해결하려면 결과를 찾기 전에 검색하려는 내용을 소문자로 강제 전환할 수 있습니다. 이렇게 하려면 검색 중인 열 옆에 새 열을 만듭니다. 함수를 입력합니다.

=TRIM(LOWER(B2))

이렇게 하면 학교 이름^{(school name)} 을 소문자로 지정하고 이름 의 왼쪽이나 오른쪽에 있을 수 있는 관련 없는 문자(공백)를 제거합니다.

Shift 키를 누른^{(Shift key and place)} 상태 에서 두 개의 수평선으로 바뀔 때까지 첫 번째 셀의 오른쪽 하단 모서리에 마우스 커서를 놓습니다 . ^{(mouse cursor)}전체 열을 자동으로 채우려면 마우스를 두 번 클릭합니다.^(Double)

마지막으로 VLOOKUP 은 이러한 셀의 텍스트가 아닌 수식을 사용하려고 하므로 모두 값으로만 ​​변환해야 합니다. 이렇게 하려면 전체 열을 복사하고 첫 번째 셀을 마우스 오른쪽 버튼으로 클릭한 다음 값만 붙여넣습니다.

이제 이 새 열에서 모든 데이터가 정리 되었으므로 B2 대신 C2에서 조회 범위^{(the lookup range)} 를 시작하여 이전 열 대신 이 새 열을 사용하도록 Excel 에서 ^(Excel)VLOOKUP 함수^{(VLOOKUP function)} 를 약간 수정합니다.

=VLOOKUP(I2,C2:G461,3,FALSE)

이제 검색을 항상 소문자로 입력하면 항상 좋은 검색 결과^{(search result)} 를 얻을 수 있음을 알 수 있습니다 .

VLOOKUP 이 대소문자 를 구분 한다는 사실을 극복하기 위한 편리한 Excel 팁 입니다.^{(handy Excel tip)}

VLOOKUP 근사치 일치

이 기사의 첫 번째 섹션에서 설명한 정확한 일치 LOOKUP 예제^{(LOOKUP example)} 는 매우 간단하지만 근사 일치는 조금 더 복잡합니다.

대략적인 일치는 숫자 범위를 검색하는 데 가장 잘 사용됩니다. 이를 올바르게 수행하려면 검색 범위^{(search range)} 를 적절하게 정렬해야 합니다. 가장 좋은 예는 숫자 등급^{(number grade)} 에 해당하는 문자 등급^{(letter grade)} 을 검색 하는 VLOOKUP 기능^{(VLOOKUP function)} 입니다.

교사가 최종 평균 열과^{(averaged column)} 함께 연중 내내 학생 숙제^{(student homework)} 성적 목록을 가지고 있는 경우 해당 최종 성적에 해당하는 문자 성적이 자동으로 표시되도록 하는 것이 좋습니다.

이것은 VLOOKUP 기능^{(VLOOKUP function)} 으로 가능합니다 . 필요한 모든 것은 각 숫자 점수 범위^{(score range)} 에 대한 적절한 문자 등급 을 포함하는 오른쪽의 ^{(letter grade)}조회 표^{(lookup table)} 입니다.

이제 VLOOKUP 기능^{(VLOOKUP function)} 과 대략적인 일치를 사용하여 올바른 숫자 범위에 해당하는 적절한 문자 등급을 찾을 수 있습니다.

이 VLOOKUP 함수에서:

• lookup_value : F2, 최종 평균 등급
• table_array : I2:J8, 문자 등급 조회 범위^{(letter grade lookup range)}
• index_column : 2, 조회 테이블 의 두 번째 열^{(lookup table)}
• [range_lookup] : TRUE, 대략적인 일치

G2 에서 ^{(G2 and press Enter)}VLOOKUP 기능^{(VLOOKUP function)} 을 완료 하고 Enter 키를 누르면 마지막 섹션에서 설명한 것과 동일한 접근 방식을 사용하여 나머지 셀을 채울 수 있습니다. 모든 글자 등급이 제대로 채워진 것을 볼 수 있습니다.

Excel 의 VLOOKUP 함수^{(VLOOKUP function)} 는 할당된 문자 점수 가 있는 ^{(letter score)}등급 범위^{(grade range)} 의 맨 아래 끝 에서 다음 문자 점수^{(letter score)} 범위의 맨 위로 검색합니다 .

따라서 "C"는 하위 범위(75)에 할당된 문자여야 하고 B는 자체 문자 범위^{(letter range)} 의 하단(최소)에 할당되어야 합니다 . VLOOKUP 은 60에서 75 사이의 값에 대해 가장 가까운 근사값으로 60(D)에 대한 결과를 "찾습니다".

^(VLOOKUP)Excel 의 ^(Excel)VLOOKUP 은 오랫동안 사용할 수 있었던 매우 강력한 기능입니다. 또한 Excel 통합 문서^{(Excel workbook)} 의 어느 곳에서나 일치하는 값을 찾는 데 유용합니다 .

그러나 월간 Office 365 구독이 있는 ^{(Office 365)}Microsoft 사용자는 이제 최신 XLOOKUP 기능에 액세스할 수 있습니다. 이 함수에는 더 많은 매개변수와 추가 유연성이 있습니다. 반기 구독 사용자는 2020년 7월^{(July 2020)} 에 업데이트가 출시될 때까지 기다려야 합니다 .

How to Use VLOOKUP in Excel

Have you ever had а large spreadsheet wіth data in Excel and need an easy way to filter and extract specific information from it? If you leаrn how to use VLOOKUP in Excel, you can do this lookup with just a single, powerful Excel function.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• 선형 Regression Trendline Excel Scatter Plot에 추가하십시오

• Excel에서 Histogram을 만드는 방법

• 안전하게 안전하게 Password Protect Excel File

• Word and Excel에서 Flowchart를 만드는 방법

• Excel Spreadsheet에서 Word에서 Create Labels의 방법

• Automatically Backup Word Document Word Document to OneDrive

• Excel 데이터 차트 작성

• Microsoft Teams에서 Background를 변경하는 방법

• Excel에서 간단한 그래프 또는 차트를 만드는 방법

• Microsoft Excel에서 Gantt Charts을 만드는 방법

• Microsoft Excel 기본 자습서 – Excel 사용 방법 배우기

• Excel의 여러 인스턴스를 여는 방법

• Excel에서 일치하는 값을 찾는 방법

• Excel에서 시트, 셀, 열 및 수식을 숨기는 방법

• Excel에서 YEARFRAC 함수를 사용하는 방법

• Excel 워크시트 셀에 주석을 추가하는 방법

• Excel에서 데이터를 필터링하는 방법

• 두 Excel 파일을 비교하고 차이점을 강조하는 방법

• Excel에서 시트와 통합 문서 간의 셀 연결

• Excel의 열 너비 및 행 높이 자동 맞춤