안녕하세요! 구글 시트로 업무 효율을 높이려 노력 중인 초보 직장인입니다. 오늘은 [2단계: 실무 응용]의 첫 번째 시간으로, 제가 업무 중에 겪었던 당혹스러운 순간을 공유하며 시작해 보려고 해요. 사번으로 이름을 찾을 때는 잘 되던 VLOOKUP이, 이름으로 사번을 찾으려니 계속 에러가 나거나 엉뚱한 값을 가져오더라고요. 왜 이런 일이 생기는 걸까요?
STEP 1. VLOOKUP이 가진 태생적인 ‘왼쪽’ 제약 이해하기
우리가 흔히 쓰는 VLOOKUP 함수는 데이터를 찾을 때 치명적인 약점이 있습니다. 바로 기준이 되는 키(Key) 값이 반드시 참조 범위의 ‘가장 왼쪽 열’에 있어야 한다는 점이에요. 만약 찾고 싶은 데이터가 기준 열보다 왼쪽에 있다면 VLOOKUP은 그 값을 인식하지 못합니다. 실무에서는 데이터 구조를 마음대로 바꿀 수 없는 경우가 많기에, 이 제약은 큰 시간 낭비로 이어지곤 하죠.
STEP 2. INDEX와 MATCH 함수의 단짝 조합 원리 파악하기
이 문제를 해결하는 열쇠가 바로 INDEX와 MATCH의 조합입니다. INDEX 함수는 지정한 범위에서 특정 위치에 있는 값을 가져오고(추출), MATCH 함수는 내가 찾는 값이 몇 번째 행이나 열에 있는지 위치를 알려주는(검색) 역할을 합니다. INDEX(범위, 행_번호) 구조 안에 행 번호 대신 MATCH를 넣어 =INDEX(불러올_값의_열, MATCH(찾을_기준값, 기준값이_있는_열, 0)) 형태로 사용하면 방향과 상관없이 자유로운 데이터 조회가 가능해집니다.
STEP 3. 실전 예제로 따라 하는 양방향 데이터 검색
예를 들어 A열에 ‘부서’, B열에 ‘이름’, C열에 ‘사번’이 있는 표가 있다고 가정해 봅시다. 이름(B열)을 기준으로 왼쪽에 있는 부서(A열)를 찾으려면 다음과 같이 입력하세요. 먼저 삽입 > 함수 메뉴를 통해 원리를 파악하거나 직접 셀에 =INDEX(A2:A10, MATCH("홍길동", B2:B10, 0))을 입력해 보세요. MATCH가 홍길동의 행 위치를 찾고, INDEX가 해당 행의 A열 값을 정확히 가져옵니다.
STEP 4. 오류 없는 입력을 위한 운영체제별 단축키 가이드
함수를 더 빠르고 정확하게 입력하기 위해 다음의 단축키를 활용해 보세요. 특히 배열 수식으로 복잡한 처리를 할 때 유용합니다. (단, 최신 구글 시트와 365 버전 엑셀에서는 XLOOKUP이라는 대안도 있어 Enter만으로도 동적 배열이 작동하는 경우가 많습니다.)
운영체제별 단축키를 정리한 표입니다.
| 운영체제 | 단축키 |
|---|---|
| 윈도우(Windows) | Ctrl + Shift + Enter (수식을 배열로 입력 시) |
| 맥(macOS) | Command(⌘) + Shift + Enter |
STEP 5. 다른 프로그램으로 데이터 옮기기와 꿀팁
구글 시트에서 작성한 이 강력한 조합 수식은 엑셀(Excel)로 그대로 복사해도 100% 호환됩니다. 만약 조회해야 할 텍스트가 255자를 초과해 VLOOKUP에서 #VALUE! 오류가 난다면, INDEX/MATCH는 글자 수 제한 없이 데이터를 정확히 불러올 수 있어 훨씬 안정적입니다. 복잡한 수식이 어렵다면 챗GPT나 제미나이(Gemini)에게 표 구조를 설명하고 수식 작성을 부탁하는 것도 좋은 방법입니다.
Q. #N/A 오류가 계속 뜨는데 왜 그런가요?
A. MATCH 함수의 마지막 옵션인 ‘0(정확히 일치)’이 빠졌는지 확인해 보세요. 또한 찾는 값과 데이터 범위의 텍스트 형식이 일치하지 않거나 공백이 포함된 경우에도 발생할 수 있습니다.
Q. 데이터 양이 수만 건인데 속도가 느려지지 않을까요?
A. VLOOKUP보다 INDEX/MATCH가 메모리 리소스를 덜 사용하는 경향이 있어 대용량 데이터에서 오히려 더 빠르고 안정적으로 작동합니다.
오늘은 실무에서 VLOOKUP의 한계를 넘어 데이터 검색의 자유를 찾는 INDEX/MATCH 조합을 배웠습니다. 다음 시간에는 [3단계: 심화 및 자동화] 과정으로, QUERY 함수를 이용해 데이터를 필터링하는 고급 기술을 함께 공부해 볼게요. 여러분은 구글 시트를 쓰면서 어떤 함수 오류 때문에 가장 답답하셨나요? 댓글로 고민을 나눠주세요!
답글 남기기