VLOOKUP으로 찾기
VLOOKUP의 마지막 인수를 빼면 Excel과 Google Sheets 모두 근사 일치를 씁니다. 구글은 FALSE를 쓰라고 권합니다.
코드가 붙은 가격표
학교 매점 가격표를 시트에 옮겨 놓았다고 해 봅시다(지어낸 예입니다). A2:A5에는 상품 코드, B2:B5에는 이름, C2:C5에는 가격이 있습니다. 계산대에서 코드를 치면 가격이 바로 나와야 합니다. 이 일을 하는 함수가 VLOOKUP입니다. 표의 첫 열에서 값을 찾고, 같은 행에 있는 다른 값을 돌려줍니다. Microsoft는 필요한 정보를 네 가지로 정리합니다. 찾을 값, 그 값이 든 범위, 범위 안에서 답이 든 열 번호, 그리고 근사 일치(TRUE)와 정확히 일치(FALSE) 가운데 어느 쪽인지입니다. =VLOOKUP(104, A2:C5, 3, FALSE)는 A열에서 104를 찾아 범위의 셋째 열에 있는 가격을 돌려줍니다. 한 번 만들어 두면 상품이 몇십 개로 늘어도 코드만 치면 가격이 나오니, 장부를 손으로 뒤질 일이 줄어듭니다.
탈이 나는 곳은 대개 두 군데입니다. 하나, 찾을 값은 범위의 첫 열에 있고 답은 그 오른쪽에 있어야 합니다. VLOOKUP은 왼쪽을 보지 않으므로 코드가 B열에 있으면 범위도 B열에서 시작해야 합니다. 둘, 열 번호는 시트의 A열이 아니라 범위의 첫 열부터 셉니다. B2:D9라면 B가 1, C가 2, D가 3입니다. 두 프로그램 도움말은 복사할 수식에 대해서도 같은 말을 합니다. $A$2:$C$5처럼 범위를 절대 참조로 써야 아래로 채워도 범위가 미끄러지지 않습니다. VLOOKUP이 이상한 답을 낼 때 가장 먼저 확인할 곳도 이 두 군데입니다.
목록에 없는 코드를 찾으면
FALSE를 쓰면 VLOOKUP은 준 값과 똑같은 값을 찾아, 처음으로 맞는 행을 돌려줍니다. 맞는 행이 없으면 #N/A입니다. 열 번호가 범위의 열 수보다 크면 #REF!, 1보다 작으면 #VALUE!가 나옵니다. 텍스트를 찾을 때 대소문자는 가리지 않지만 공백은 가립니다. 구글 도움말은 " Apple", "Apple ", "Apple"이 서로 다른 세 값이라고 짚습니다. 다른 반에서 받은 명단을 붙여 넣었더니 이름 끝에 공백이 붙어 있었다면(지어낸 예입니다), 눈으로는 같은 이름도 VLOOKUP에는 다른 값입니다. 텍스트로 저장된 숫자도 그 숫자와 다른 값이라서, '104로 입력한 코드는 104로 찾아지지 않습니다.
정확히 일치에서는 두 프로그램 모두 와일드카드 ?와 *를 씁니다. ?는 아무 글자 하나, *는 이어진 아무 글자 여러 개를 뜻하므로 "Ora*"는 Orange를 찾습니다. 아직 가격표에 오르지 않은 코드처럼 #N/A가 나와도 이상하지 않은 경우라면 찾기 수식을 IFNA로 감쌉니다. =IFNA(VLOOKUP(E2, $A$2:$C$5, 3, FALSE), "not found")처럼 쓰고, IFNA는 두 프로그램 모두에 있습니다. IFERROR도 되지만 모든 오류를 가려 버려서, 열 번호를 잘못 쓴 것처럼 꼭 봐야 할 오류까지 숨깁니다. Microsoft의 #VALUE! 안내도 같은 이유로 IFERROR를 조심하라고 합니다. 와일드카드를 쓸 때는 "김*"처럼 여러 행에 맞는 값이라도 VLOOKUP이 처음으로 맞는 행 하나만 돌려준다는 데 주의하세요.
끌어서 돌리고, 휠이나 두 손가락으로 확대하세요.
정확한 코드 대신 구간으로 찾기
넷째 인수에 TRUE를 쓰거나 아예 빼면 근사 일치가 됩니다. 두 프로그램 모두 이쪽이 기본값입니다. 구글 도움말의 설명대로라면 함수는 첫 열을 위에서 아래로 훑다가, 찾는 값보다 큰 값을 만나면 그 바로 앞 행에서 멈춥니다. 구간을 나눌 때 딱 맞는 방식입니다. 첫 열에 0, 60, 70, 80, 90이 있는 성적표에서 85점은 80을 지나 90을 만나고, 80 행의 B를 돌려줍니다. 택배 무게별 요금표나 나이별 입장료표도 같은 모양입니다(지어낸 예입니다). 단, 첫 열은 작은 값부터 큰 값 순서로 정렬되어 있어야 하고, 첫 행보다 작은 값을 찾으면 #N/A가 나옵니다. 정렬되지 않은 열에서는 답을 믿을 수 없습니다. 구간을 찾는 경우가 아니면 FALSE를 쓰라고 구글이 권하는 이유입니다.
끌어서 돌리고, 휠이나 두 손가락으로 확대하세요.
| Microsoft Excel | Google Sheets | |
|---|---|---|
| 넷째 인수 이름 | range_lookup | is_sorted |
| 빼면 기본값 | TRUE(근사 일치) | TRUE(근사 일치) |
| 도움말의 권고 | 첫 열을 정렬하거나 FALSE 사용 | FALSE 권장 |
| XLOOKUP | Microsoft 365와 Excel 2021부터, 2016·2019에는 없음 | 있음 |
| 읽을 수 없는 수식 | 안내 창으로 입력 거부 | #ERROR! |
| 순환 참조 | 경고 후 0이나 마지막 값 | #REF! |
| 자릿수 없는 ROUND | 받지 않음 | 정수로 반올림 |
| 함수 하나의 인수 | 255개까지 | 제한 없음 |
이제 두 프로그램에는 XLOOKUP도 있습니다. 찾을 범위와 결과 범위를 따로 받으므로 답이 왼쪽에 있어도 되고, 따로 정하지 않으면 정확히 일치로 찾습니다. 다만 Microsoft에 따르면 Excel 2016과 Excel 2019에는 XLOOKUP이 없습니다. 그 버전에서도 열려야 하는 파일이라면 VLOOKUP을 그대로 두는 편이 낫습니다. 학교나 집 컴퓨터에 어떤 버전의 Excel이 깔려 있는지 먼저 확인해 두면 좋습니다. 위의 표에는 이 과정에서 만난 두 프로그램의 차이를 모아 두었습니다.
- 코드를 찾으면서 FALSE 빼먹기 기본값이 근사 일치라서, 목록에 없는 코드가 #N/A 대신 바로 위 행의 가격을 들고 나올 수 있습니다. 가격표에서는 이런 답이 오류 표시보다 위험합니다.
- 열 번호를 시트의 A열부터 세기 열 번호는 범위의 첫 열부터 셉니다. 범위가 C2:F20이면 E열은 5가 아니라 3입니다. 범위를 바꾸면 열 번호도 다시 세어야 합니다.
- 범위에 $ 없이 찾기 수식 채우기 A2:C5가 A3:C6, A4:C7로 내려가서, 아래 행은 표 위쪽의 코드를 못 찾게 됩니다. $A$2:$C$5로 고정하세요.
- 모든 찾기 수식을 IFERROR로 감싸기 예상한 #N/A뿐 아니라 열 번호를 잘못 써서 생긴 #REF!까지 숨깁니다. IFNA는 #N/A만 가립니다.
- A2:B5에 코드 101, 103, 104, 107과 품목 Apple, Banana, Orange, Pear가 있습니다. =VLOOKUP(103, A2:B5, 2, FALSE)의 결과는 무엇일까요?
- 같은 표입니다. =VLOOKUP(102, A2:B5, 2, FALSE)의 결과는 무엇일까요?
- #N/A
- Apple
- Banana
- #REF!
- 같은 표입니다. =VLOOKUP(102, A2:B5, 2, TRUE)의 결과는 무엇일까요?
- 같은 표입니다. =VLOOKUP(103, A2:B5, 3, FALSE)의 결과는 무엇일까요?
- #REF!
- #N/A
- #VALUE!
- Banana
- 범위가 C2:F20입니다. E열의 값을 돌려받으려면 열 번호가 몇이어야 할까요?
- 성적표가 0 F, 60 D, 70 C, 80 B, 90 A입니다. 85점을 근사 일치로 찾으면 무엇이 나올까요?
- 같은 표입니다. 딱 90점이면 무엇이 나올까요?
- 넷째 인수를 빼면 어떻게 될까요?
- 두 프로그램 모두 근사 일치
- 두 프로그램 모두 정확히 일치
- Excel은 정확히, Sheets는 근사
- 오류
- 표 범위를 $A$2:$C$5로 써야 하는 이유는 무엇일까요?
- 수식을 아래로 채워도 범위가 미끄러지지 않도록
- 표를 정렬하려고
- 대문자만 찾으려고
- 텍스트를 돌려받으려고
- B2:B5에 품목, C2:C5에 가격이 있습니다. =IFNA(VLOOKUP("Pencil", B2:C5, 2, FALSE), "not found")의 결과는 무엇일까요?
- XLOOKUP이 없는 Excel 버전은 어느 것일까요?
- Excel 2016과 Excel 2019
- Microsoft 365
- 웹용 Excel
- Excel 2021
- 정확히 일치는 VLOOKUP(값, 범위, 열 번호, FALSE)입니다.
- 찾을 값은 첫 열에 있어야 하고 열 번호는 범위부터 셉니다.
- TRUE(또는 생략)는 근사 일치로, 첫 열이 정렬되어 있어야 하며 값보다 크지 않은 마지막 행을 돌려줍니다.
- 범위는 절대 참조로, 예상되는 #N/A는 IFNA로, 구간이 아니면 FALSE를 씁니다.
이 과정의 출처 (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
확인한 날: 2026-10-19