엑셀 | VLOOKUP을 언제 버려야 하나

남이 만든 표에서 값 하나 끌어오는 일은 대체로 VLOOKUP으로 시작한다. 그러다 어느 순간 안 된다.

찾을 값이 표 맨 왼쪽이 아니거나, 회사 PC에는 XLOOKUP이 없거나, 멀쩡하던 식이 어느 날부터 다른 값을 뱉거나. 세 함수를 다 외울 필요는 없는 것 같고, 어디서 갈라지는지만 알아 두면 될 듯 싶다.

우선 결론부터

• 엑셀 365·2021·2024·웹 → XLOOKUP
• 엑셀 2016·2019 → INDEX+MATCH
• 찾을 값이 맨 왼쪽 열이고 오른쪽 값만 가져오면 → VLOOKUP도 충분

XLOOKUP은 2019년 말 Microsoft 365에 들어갔고 영구 라이선스인 2021·2024와 웹용에도 붙었다. 2016·2019에는 끝내 안 들어왔다. 집 노트북에서는 되는데 회사에서 #NAME?이 뜨는 이유가 이거다.

VLOOKUP은 인수 네 개가 전부다

코드표를 A2:C4에 두고, E2에 넣은 코드로 단가를 끌어오는 식이다. 네 번째 인수 FALSE가 “정확히 일치”.

=VLOOKUP(E2, $A$2:$C$4, 3, FALSE)

F2에 8200이 들어왔다. FALSE를 빼거나 TRUE로 두면 근사값 일치가 되는데, 표가 정렬돼 있지 않으면 엉뚱한 값을 멀쩡한 얼굴로 돌려준다.

범위에 $를 거는 것도 잊으면 안 된다. 식을 아래로 끌어 복사할 때 범위가 같이 밀려 내려가서 아래쪽 행만 #N/A가 뜬다.

진짜 문제는 조용히 틀리는 쪽이다

VLOOKUP이 막히는 자리 중 제일 자주 만나는 건 왼쪽 조회다. 받은 파일이 “거래처명 - 코드 - 단가” 순인데 거래처명으로 코드를 찾아야 하면 손을 못 댄다. 같은 표에서 셋을 나란히 돌려 봤다.

VLOOKUP만 #N/A다. 첫 열에서만 찾아 오른쪽 값을 돌려주는 구조라 왼쪽은 방법이 없다.

더 고약한 건 오류조차 안 나는 경우다. 표 중간에 “비고” 열을 하나 끼워 넣으면 열 번호 3이 가리키던 자리가 단가에서 비고로 밀린다. 식은 그대로다.

단가 자리에 “거래중지”가 들어와 있다. 오류 표시가 없으니 눈으로 훑어서는 못 잡는다. 아무래도 VLOOKUP을 슬슬 놓게 되는 진짜 이유는 이쪽인 것 같다.

XLOOKUP은 그 불편을 대부분 없앴다

찾을 범위와 반환 범위를 따로 주니까 방향 제약이 없다. 기본이 정확히 일치라 FALSE를 까먹을 일도 없고, 네 번째 인수에 텍스트를 넣으면 못 찾았을 때 #N/A 대신 그게 뜬다. IFERROR로 감쌀 이유가 사라진다.

=XLOOKUP(E2, $B$2:$B$4, $A$2:$A$4, "코드없음")

여섯 번째 인수에 -1을 넣으면 아래에서 위로 훑는다. 같은 키가 여러 번 쌓이는 로그성 데이터에서 마지막 값을 뽑을 때 쓴다. 가로로 정리된 표도 범위를 행 방향으로 주면 그만이라, HLOOKUP은 따로 외울 이유가 없어졌다.

단점은 하나. 2016·2019에서는 안 된다.

버전을 모르겠으면 INDEX+MATCH

MATCH가 “그 값이 몇 번째냐”를 돌려주고 INDEX가 그 자리의 값을 꺼낸다. MATCH의 세 번째 인수 0이 정확히 일치다.

=INDEX($A$2:$A$4, MATCH(E2, $B$2:$B$4, 0))

방향 제약 없고, 열을 끼워 넣어도 범위 참조라 잘 안 깨지고, 엑셀 95 시절부터 다 된다. 대신 식이 길고 처음 보면 헷갈린다. 남이 유지보수할 시트라면 가독성에서 XLOOKUP에 밀리는 건 어쩔 수 없다.

오류는 대체로 형식 문제다

#N/A는 진짜 없거나, 있어 보이는데 형식이 다른 경우다. 한쪽은 텍스트 "1001", 다른 쪽은 숫자 1001이면 엑셀은 다른 값으로 본다. 앞뒤에 붙은 보이지 않는 공백도 마찬가지고. TRIM이나 VALUE로 맞춰 준다.

#REF!는 열 번호가 범위의 열 수보다 클 때. #NAME?은 함수 이름 오타거나, 2016에서 XLOOKUP을 썼을 때다.

값이 없을 수도 있는 자리라면 IFERROR보다 IFNA가 안전하다고 본다. IFERROR는 모든 오류를 다 먹어 버려서 식 자체의 실수까지 가린다.

=IFNA(VLOOKUP(E2, $A$2:$C$4, 3, FALSE), "조회 안 됨")

결론. 회사 PC에 =XLOOKUP( 까지만 쳐 보면 된다

자동완성이 뜨면 XLOOKUP으로 가고, 안 뜨면 INDEX+MATCH를 외워 두는 편이 낫겠다.

그리고 지금 열려 있는 시트에 VLOOKUP이 있으면 FALSE와 $ 두 가지만 확인해 보자. 둘 중 하나라도 빠져 있으면 언젠가 조용히 틀린 값을 뱉을 폭탄이다. 위에서 본 “거래중지”처럼.