VLOOKUP 함수는 Google 스프레드시트에서 그 중요성에 비해 상대적으로 덜 알려진 기능 중 하나입니다. 이 함수를 활용하면 스프레드시트 내의 서로 다른 데이터 집합들을 단일 검색 값을 기준으로 찾아 연결할 수 있습니다. 여기서는 VLOOKUP의 사용법을 자세히 알아보겠습니다.
Microsoft Excel과는 다르게 Google 스프레드시트에서는 VLOOKUP 마법사를 제공하지 않으므로, 사용자가 직접 수식을 입력해야 합니다.
Google 스프레드시트 VLOOKUP 작동 원리
VLOOKUP은 처음에는 복잡하게 느껴질 수 있지만, 작동 방식을 이해하면 매우 간단한 함수입니다. VLOOKUP 함수를 사용하는 수식은 총 4개의 인수로 구성됩니다.
첫 번째 인수는 찾고자 하는 검색 기준 값이며, 두 번째 인수는 검색을 수행할 셀 범위(예: A1:D10)입니다. 세 번째 인수는 검색 대상 범위 내에서 반환할 데이터가 있는 열의 번호입니다. 이때 범위의 첫 번째 열은 1, 두 번째 열은 2와 같이 번호가 매겨집니다.
마지막 네 번째 인수는 검색 대상 열이 정렬되어 있는지 여부를 나타냅니다.
네 번째 인수는 검색 기준 값과 가장 유사한 값(근사치)을 찾을 때만 중요합니다. 만약 검색 값과 정확히 일치하는 항목을 찾고 싶다면, 이 인수를 FALSE로 설정해야 합니다.
VLOOKUP 함수 사용의 예를 들어보겠습니다. 회사 스프레드시트에 두 개의 시트가 있다고 가정해 봅시다. 하나는 제품 목록(각각 ID 번호와 가격 포함)이고, 다른 하나는 주문 목록입니다.
여기서 VLOOKUP의 검색 값으로 제품 ID 번호를 사용하면 각 제품의 가격을 신속하게 찾아낼 수 있습니다.
VLOOKUP 사용 시 주의해야 할 점은, 열 인덱스 번호를 기준으로 왼쪽 방향에 있는 데이터는 검색할 수 없다는 것입니다. 대부분의 경우 검색 키의 왼쪽에 있는 열 데이터는 무시하거나, 검색 키 데이터를 첫 번째 열에 위치시켜야 합니다.
단일 시트에서의 VLOOKUP 활용
단일 시트 내에 두 개의 데이터 테이블이 있다고 가정해 봅시다. 첫 번째 테이블은 직원 이름, ID 번호, 그리고 생년월일로 구성되어 있습니다.
두 번째 테이블에서는 첫 번째 테이블의 기준 데이터(이름, ID 번호, 생년월일)를 사용하여 데이터를 검색할 수 있습니다. 이 예시에서는 특정 직원 ID 번호를 사용하여 해당 직원의 생년월일을 VLOOKUP으로 찾아볼 것입니다.
이를 위한 VLOOKUP 수식은 =VLOOKUP(F4, A3:D9, 4, FALSE)와 같이 표현할 수 있습니다.
수식을 분석해 보면, VLOOKUP 함수는 F4 셀의 값(123)을 검색 키로 사용하며, A3부터 D9까지의 셀 범위를 검색합니다. 그리고 이 범위 내에서 네 번째 열(열 D, “생년월일”)의 데이터를 반환하며, 정확한 일치를 원하므로 마지막 인수는 FALSE로 설정됩니다.
이 경우 ID 번호가 123인 직원의 생년월일 1971년 12월 19일(DD/MM/YY 형식)이 반환됩니다. 테이블에 “성” 열을 추가하여 예시를 확장하여 생년월일을 실제 사람과 연결할 수도 있습니다.
이러한 경우, 수식을 약간만 변경하면 됩니다. 예를 들어, 셀 H4에 입력한 =VLOOKUP(F4, A3:D9, 3, FALSE) 수식은 ID 번호 123과 일치하는 성을 찾아줍니다.
즉, 생년월일을 반환하는 대신, 첫 번째 열(“ID”)에 있는 ID 값과 일치하는 세 번째 열(“성”)의 데이터를 반환합니다.
여러 시트에서의 VLOOKUP 활용
앞선 예시에서는 단일 시트 내의 데이터 집합을 사용했지만, VLOOKUP 함수를 활용하면 스프레드시트의 여러 시트에 걸쳐 데이터를 검색할 수 있습니다. 테이블 A의 정보가 “Employees”라는 시트에, 테이블 B의 정보가 “Birthdays”라는 시트에 있다고 가정해 봅시다.
일반적인 셀 범위(예: A3:D9)를 사용하는 대신, 빈 셀을 클릭한 다음 =VLOOKUP(A4, Employees!A3:D9, 4, FALSE)를 입력하면 됩니다.
셀 범위의 시작 부분에 시트 이름을 추가하여 (Employees!A3:D9) VLOOKUP 수식이 다른 시트에 있는 데이터를 검색에 활용할 수 있게 합니다.
VLOOKUP과 와일드카드 사용
이전 예시에서는 정확한 검색 키 값을 사용하여 일치하는 데이터를 찾았습니다. 만약 정확한 검색 키 값을 모르는 경우, VLOOKUP 함수에서 물음표(?)나 별표(*)와 같은 와일드카드를 사용할 수도 있습니다.
이 예시에서는 앞서 사용한 데이터 세트를 활용하되, “이름” 열을 A열로 옮기고 이름의 일부와 별표 와일드카드를 사용하여 직원의 성을 검색해 보겠습니다.
이름의 일부를 사용하여 성을 검색하는 VLOOKUP 수식은 =VLOOKUP(B12, A3:D9, 2, FALSE)이며, 검색 키 값은 B12 셀에 있습니다.
아래 예시에서 셀 B12의 “Chr*”는 샘플 조회 테이블에서 “Geek” 성과 일치합니다.
VLOOKUP으로 가장 가까운 일치값 찾기
VLOOKUP 수식의 마지막 인수를 사용하면 검색 키 값과 정확히 일치하는 값 또는 가장 가까운 일치하는 값을 찾을 수 있습니다. 이전 예시에서는 정확한 일치값을 찾았기 때문에 이 값을 FALSE로 설정했습니다.
가장 가까운 값을 찾기 위해서는 VLOOKUP의 마지막 인수를 TRUE로 변경해야 합니다. 이 인수는 검색 범위의 정렬 여부를 지정하므로, 검색 열이 오름차순으로 정렬되어 있는지 확인해야 합니다. 그렇지 않으면 올바르게 작동하지 않습니다.
아래 표에는 품목 이름과 가격이 함께 나열되어 있습니다(A3~B9). 가격은 가장 낮은 가격부터 높은 가격 순으로 정렬되어 있습니다. 주어진 예산은 총 17달러(셀 D4)이며, VLOOKUP 함수를 사용하여 이 예산 내에서 가장 저렴한 품목을 찾아보겠습니다.
이 예시를 위한 VLOOKUP 수식은 =VLOOKUP(D4, A4:B9, 2, TRUE)입니다. 이 VLOOKUP 수식은 검색 값 자체보다 낮은 가장 가까운 값을 찾도록 설정되어 있으므로, 설정된 예산 17달러 이하의 품목만 검색할 수 있습니다.
이 예시에서 17달러 미만의 가장 저렴한 품목은 15달러짜리 가방이며, D5 셀의 결과로 VLOOKUP 수식에 의해 반환된 값입니다.
