Google 스프레드시트의 VLOOKUP(): 사용 방법과 예제

VLOOKUP()은(는) Google 스프레드시트에서 한 열에서 값을 찾아 같은 행의 다른 열에서 일치하는 값을 반환하는 함수입니다. 찾을 값을 지정하고, 어디에서 찾을지 범위를 알려 주면 필요한 데이터를 가져옵니다.

수작업으로 찾고 매칭하기엔 시간이 너무 오래 걸리는 대규모 데이터셋에서 특히 유용합니다. 하지만 공통 값을 기준으로 두 표의 데이터를 연결해야 할 때마다 VLOOKUP()은 기본으로 사용하는 함수입니다.

이 가이드에서는 구문, 단계별 사용 방법, 잘못된 결과가 나오는 원인, 그리고 INDEX MATCH()나 XLOOKUP() 같은 대안을 선택할 시점을 알아봅니다.

Google 스프레드시트에서 VLOOKUP()의 구문은 무엇인가요?

VLOOKUP()의 구문은 다음과 같습니다:

=VLOOKUP(search_key, range, index, [is_sorted])

설명: 

• search_key는 찾고자 하는 값입니다

• range는 검색이 이루어질 표 범위입니다

• index는 결과를 반환할 열 번호입니다

• [is_sorted]는 정확히 일치할지 여부를 지정합니다

Google 스프레드시트에서 VLOOKUP()은 어떻게 하나요?

Google 스프레드시트에서 VLOOKUP()을 사용하려면:

1. 조회할 값을 결정하세요: ID, 이름 등 고유한 값이면 됩니다.

2. 표 범위를 정하세요: 조회 값이 들어 있는 열이 해당 범위의 첫 번째 열이 되도록 하세요.

3. 반환할 데이터를 선택하세요: 결과가 있는 열입니다(예: 이름, 가격, 상태).

4. 새 셀에 VLOOKUP() 수식을 입력하세요: 조회 값, 표 범위, 반환할 열을 참조합니다.

5. 정확 일치 또는 근사 일치(FALSE 또는 TRUE)를 사용하세요: 대부분의 경우 잘못된 결과를 피하려면 정확 일치를 사용하는 것이 좋습니다(뒤에서 자세히 설명).

다음과 같은 표가 있다고 합시다:

그리고 ID 102에 해당하는 이름을 찾고 싶다면 다음을 사용합니다:

=VLOOKUP(102, A2:B4, 2, FALSE)

이 수식은 Google 스프레드시트에 다음을 지시합니다:

• 열 A에서 102를 찾기
• 열 B의 값을 반환하기
• 정확 일치 사용하기

결과로 Sarah가 반환됩니다.

VLOOKUP() 예시. 이미지: 작성자

VLOOKUP() 작동 방식(단계별 논리)

VLOOKUP()은(는) 간단한 수직 검색 과정을 따릅니다. 선택한 범위의 첫 번째 열에서 시작해, 아래로 한 행씩 스캔하며 첫 번째 일치 항목을 찾습니다.

일치 항목을 찾으면 같은 행에 머무른 채, 지정한 열로 이동하여 그 값을 반환합니다.

단계별로 보면 다음과 같습니다:

• 범위의 첫 번째 열에서 시작
• 해당 열을 아래로 검색
• 첫 번째 일치 값을 찾음
• 같은 행에서 옆으로 이동
• 지정된 열의 데이터를 반환

예를 들어 직원 ID E1007의 직함을 찾고 싶다고 합시다. 다음 수식을 사용합니다:

=VLOOKUP("E1007", A:G, 5, FALSE)

Google 스프레드시트의 VLOOKUP(). 이미지: 작성자

이 수식은 다음을 수행합니다:

• 열 A에서 E1007을 찾습니다

• 같은 행에서 옆으로 이동합니다

• 5번째 열(직함)을 반환합니다 

• 결과로 Software Engineer를 반환합니다.

참고: VLOOKUP()은(는) 왼쪽에서 오른쪽으로만 검색할 수 있습니다. 조회 열이 범위의 첫 번째 열이 아니라면 함수가 작동하지 않습니다. 이 경우 데이터를 재배열하거나 다른 함수를 사용해야 합니다. 이에 대해서는 뒤에서 다룹니다.

VLOOKUP()의 정확 일치 vs 근사 일치

정확 일치는 값이 동일할 때만 결과를 반환합니다. 근사 일치는 정확한 값을 찾지 못할 경우, 보통 정렬된 데이터를 기준으로 가장 가까운 값을 반환합니다.

VLOOKUP()에서는 마지막 인수로 이를 제어합니다. 마지막 인수인 FALSE 또는 TRUE는 많은 분이 실수하는 부분입니다. TRUE는 그럴듯해 보이지만 그렇지 않습니다.

• FALSE는 정확 일치만 허용합니다. 값이 없으면 #N/A가 나오는데, 사실 이것이 더 도움이 됩니다. 
• TRUE는 정확 일치가 없을 때 가장 가까운 더 작은 값을 반환합니다. 대체 기능처럼 들리지만, 조용히 틀린 답을 주는 것에 가깝습니다. 또한 데이터가 정렬되어 있다고 가정합니다. 정렬되어 있지 않다면 결과는 예측 불가입니다. 

세율 구간이나 학점 구간처럼 근사값이 설계상 올바른 동작인 계층형 조회에서만 TRUE를 사용하시길 권합니다. 그 외에는 FALSE를 사용하세요.

정확 일치(FALSE)

이는 VLOOKUP()에 “이 값이 정확히 일치할 때만 결과를 반환하라”고 지시합니다. 예를 들어 =VLOOKUP("E1005", A:G, 7, FALSE)는 E1005를 찾아 7번째 열의 급여를 반환하고 결과로 90000을 줍니다.

값이 없으면 #N/A 오류가 발생합니다. 

VLOOKUP()의 정확 일치. 이미지: 작성자

근사 일치(TRUE)

이는 VLOOKUP()에 “정확한 값을 찾지 못하면 그보다 바로 작은 가장 가까운 값을 반환하라”고 지시합니다. 도움이 될 때도 있지만, 때때로 조용히 잘못된 답을 줍니다.

예를 들어, E1011은 데이터셋에 없습니다. 마지막 직원 ID는 E1010입니다. 따라서 =VLOOKUP("E1011", A:G, 7, TRUE)를 사용하면 VLOOKUP()은 오류를 반환하지 않습니다. 대신 가장 가까운 더 작은 일치값인 E1010의 값을 반환합니다. 셀에는 88000이 표시됩니다.

즉 E1011이 없어도, TRUE가 근사 일치를 허용하므로 VLOOKUP()은 여전히 결과를 제공합니다.

VLOOKUP()의 근사 일치. 이미지: 작성자

이제 같은 데이터가 정렬되어 있지 않다고 가정하고, 다음처럼 존재하는 직원 ID를 수식에 입력한다고 해봅시다: =VLOOKUP("E1011", A:G, 7, TRUE).

이번에는 조회 열이 정렬되어 있지 않고 근사 일치가 정렬된 데이터에 의존하기 때문에, 수식이 잘못된 급여를 반환합니다.

정렬되지 않은 목록 때문에 VLOOKUP() 근사 일치에서 오류가 발생. 이미지: 작성자

VLOOKUP()의 흔한 오류와 해결 방법

VLOOKUP()에서 자주 발생하는 문제와 해결책을 살펴보겠습니다: 

#N/A 오류

#N/A 오류는 VLOOKUP()이 요청한 값을 찾지 못했다는 의미입니다.

예를 들어 다음 수식을 사용한다고 합시다: =VLOOKUP("E9999", A:G, 7, FALSE)

 E9999가 범위의 첫 번째 열에 없다면 수식은 #N/A를 반환합니다.

시트에서 값이 다르게 입력된 경우(앞뒤 공백, 텍스트 서식 문제 등)에도 발생할 수 있습니다.

해결 방법:

• 값이 범위의 첫 번째 열에 존재하는지 확인

• 가능하면 값을 직접 입력하지 말고 올바른 셀 참조 사용

• 텍스트 값을 하드코딩할 경우 큰따옴표 추가

• 데이터의 선행/후행 공백 제거

• 정확 일치가 필요할 때만 FALSE 사용 

잘못된 열 인덱스

열 인덱스는 선택한 범위 내에서 VLOOKUP()이 어느 열에서 반환할지 지정합니다. 인덱스 번호가 너무 크면 수식은 #REF!를 반환합니다.

=VLOOKUP("E1005", A:G, 8, FALSE)는 범위 A:G가 7개 열만 포함하므로 #REF!를 반환합니다.

잘못된 열 인덱스로 인한 오류. 이미지: 작성자

열 번호가 유효하지만 엉뚱한 열을 가리키는 경우에도 잘못된 결과가 나올 수 있습니다.

예:

=VLOOKUP("E1005", A:G, 5, FALSE)

이는 급여가 아니라 직함을 반환합니다.

해결 방법:

• 선택한 범위의 첫 번째 열부터 열을 세세요
• 열 번호가 반환하려는 값과 일치하는지 확인
• 열이 범위 안에 존재하는지 확인

근사 일치 문제

TRUE를 사용하면 VLOOKUP()은 정확 일치 대신 가장 가까운 더 작은 값을 찾습니다.

=VLOOKUP("E1011", A:G, 7, TRUE)라고 입력했고 데이터셋에 E1011이 없다면, 오류 대신 E1010의 값인 88000을 반환합니다.

겉보기에는 맞는 것처럼 보여도, 정확 일치는 아닙니다.

해결 방법:

• 정확한 결과가 필요할 땐 FALSE 사용

• 데이터가 정렬되어 있고 근사 결과가 허용될 때만 TRUE 사용

범위 문제

VLOOKUP()은(는) 선택한 범위가 수식의 의도와 맞지 않으면 작동하지 않을 수 있습니다.

예를 들어 =VLOOKUP("E1005", A:C, 7, FALSE)는 범위 A:C가 3개 열뿐인데 7번째 열을 요청하므로 #REF!를 반환합니다.

또 다른 흔한 문제는 조회 열이 범위의 첫 번째 열이 아닌 경우입니다.

다음 수식을 보세요:

=VLOOKUP("E1005", B:G, 7, FALSE)

여기서 범위의 첫 번째 열은 A가 아니라 B입니다. VLOOKUP()은(는) 선택한 범위의 첫 번째 열만 검색하므로 E1005를 찾을 수 없습니다.

해결 방법:

• 조회 열이 범위의 첫 번째 열인지 확인
• 반환하려는 열이 범위에 포함되어 있는지 확인
• 수식을 확정하기 전에 선택한 범위를 다시 확인

잘못된 범위로 인한 오류. 이미지: 작성자

Google 스프레드시트에서 시트 간 VLOOKUP()

VLOOKUP()에서 범위 앞에 시트 이름을 추가하면 다른 시트의 데이터를 가져올 수 있습니다.

형식은 다음과 같습니다:

=VLOOKUP(search_key, SheetName!range, index, FALSE)

SheetName! 부분은 어느 시트에서 검색할지 알려 줍니다.

데이터가 Employees라는 시트에 있고, 다른 시트에서 작업 중이라고 가정합시다. 직원 ID E1005의 부서를 찾으려면 다음을 사용합니다:

=VLOOKUP("E1005", Employees!A:G, 4, FALSE)

설명:

• Employees!A:G는 Employees 시트를 조회합니다

• 4는 Department 열을 반환합니다

수식은 E1005의 부서를 반환합니다.

다른 시트 참조하기(VLOOKUP()). 이미지: 작성자

시트 이름에 공백이 있을 때

시트 이름에 공백이 있으면 다음처럼 작은따옴표로 감싸세요: 

=VLOOKUP("E1005", 'Employee Data'!A:G, 7, FALSE)

작은따옴표가 없으면 수식이 작동하지 않습니다.

Google 스프레드시트에서 VLOOKUP() vs. INDEX MATCH

VLOOKUP()은(는) 고정된 열에서 위치 기반으로 데이터를 반환하는 반면, INDEX MATCH는 행과 열 참조를 직접 사용해 값을 찾으므로 표 구조가 바뀌어도 작동합니다.

VLOOKUP: 쓰기 빠르지만 쉽게 깨짐

이미 보았듯 =VLOOKUP("E1005", A:G, 7, FALSE)는 E1005의 급여를 찾습니다. 

다음과 같은 경우 잘 작동합니다:

• 조회 열이 왼쪽에 있을 때
• 표 구조가 변하지 않을 때

구조가 바뀌면 문제가 시작됩니다.

예를 들어:

• 새 열을 삽입함
• 급여 열이 7에서 8로 이동함
• 수식은 여전히 7을 사용함

이제 오류 없이 잘못된 값을 반환합니다.

INDEX MATCH: 더 길지만 안정적

같은 조회를 INDEX MATCH로 보면:

=INDEX(G:G, MATCH("E1005", A:A, 0))

설명:

• MATCH()가 열 A에서 E1005의 위치를 찾습니다

• INDEX()가 그 위치의 열 G 값을 반환합니다

이 방법은 열 번호에 의존하지 않으므로 표가 바뀌어도 계속 작동합니다.

INDEX MATCH가 VLOOKUP()보다 데이터를 안정적으로 처리. 이미지: 작성자

실제로 도움이 되는 경우

데이터가 왼쪽에서 오른쪽으로 정렬되어 있지 않을 때 INDEX MATCH를 사용하세요. 예를 들어 열 A에 직원 ID가 있고 열 C에 성이 있을 때, 성으로 검색해 직원 ID를 반환하는 것은 오른쪽에서 왼쪽으로 조회하는 것입니다.

VLOOKUP()은(는) 표를 바꾸지 않으면 이를 처리할 수 없지만, INDEX MATCH는 바로 처리합니다:

=INDEX(A:A, MATCH("Wilson", C:C, 0))

데이터를 재배열할 필요가 없습니다.

Google 스프레드시트에서 VLOOKUP() vs XLOOKUP

VLOOKUP()은(는) 고정된 구조 내에서만 검색하여 한계가 있는 반면, XLOOKUP()은 어떤 방향으로든 조회가 가능합니다. 2022년에 Google 스프레드시트에 도입되었으며, 이제 많은 경우 더 나은 선택입니다.

예를 들어 성 Wilson으로 직원 ID를 찾고 싶다고 합시다. 이는 오른쪽에서 왼쪽으로 조회하는 것입니다. VLOOKUP()은(는) 표를 재배열하지 않으면 이를 처리할 수 없지만, XLOOKUP()은 가능합니다:

=XLOOKUP("Wilson", C:C, A:A)

이 수식은:

• 성(열 C)을 검색하고
• 직원 ID(열 A)를 반환합니다

Google 스프레드시트의 XLOOKUP(). 이미지: 작성자

XLOOKUP()이 더 나은 이유:

• 어떤 방향(왼쪽/오른쪽)으로도 작동
• 열 번호를 사용하지 않음
• 기본값이 정확 일치
• 일치 항목이 없을 때 사용자 지정 출력 허용 

마무리 생각 

한 가지 조언을 드리자면: 연습용 시트가 아니라 실제로 중요한 스프레드시트에 VLOOKUP()을 구축해 보세요. 데이터가 중요할 때 오류는 다르게 느껴집니다. TRUE가 조용히 잘못된 급여를 반환하는 순간, 왜 FALSE가 존재하는지 잊지 않게 됩니다.

익숙해지면 다양한 일치 유형을 시도하고, 범위를 조정하고, 결과가 말이 안 되기 시작하는 지점을 눈여겨보세요. 보통 그때가 INDEX MATCH()나 XLOOKUP() 같은 더 유연한 옵션을 쓸 시점입니다.