엑셀 vlookup 다중조건으로 값 찾는 방법

얼마 전 가격표에서 ‘USB-C 케이블’을 찾았는데, VLOOKUP 결과가 주문서 금액과 달랐습니다. 같은 상품에 1m와 2m 옵션이 따로 있었는데 상품명만 검색한 탓이었죠. 이런 경우에는 상품명과 옵션을 함께 확인해야 원하는 가격을 가져올 수 있습니다.

VLOOKUP에 조건 두 개를 넣을 수 있을까

VLOOKUP은 지정한 범위의 첫 번째 열에서 검색값 하나를 찾습니다. 그래서 상품명과 옵션을 각각 조건으로 넣는 칸은 없습니다. 가장 다루기 쉬운 방법은 두 값을 하나의 검색값으로 합친 ‘보조 열’을 만드는 것입니다. 마이크로소프트의 함수 안내에서도 VLOOKUP은 검색값이 조회 범위의 첫 번째 열에 있어야 한다고 설명합니다.

예를 들어 B열에 상품명, C열에 옵션, D열에 가격이 있다고 해볼게요. 2행은 USB-C 케이블·1m·5,900원, 3행은 USB-C 케이블·2m·7,900원입니다. 상품명만 찾으면 두 행을 구별하지 못합니다. A열에 ‘USB-C 케이블|1m’처럼 두 조건을 붙인 값을 만들면 어느 행의 가격을 가져올지 분명해집니다.

보조 열을 만들고 수식 입력하기

A2 셀에 =B2&"|"&C2를 입력하고 데이터의 마지막 행까지 채웁니다. 가운데의 세로 막대는 구분자입니다. 그냥 B2&C2로 붙여도 되지만, ‘AB’와 ‘C’를 합친 값과 ‘A’와 ‘BC’를 합친 값은 둘 다 ‘ABC’가 됩니다. 구분자를 넣으면 이런 혼동을 줄일 수 있어요. 상품명이나 옵션 자체에 세로 막대가 들어간다면 데이터에 없는 다른 구분자를 고르면 됩니다.

이제 H2에 찾을 상품명, H3에 옵션을 입력하고 H4에 =IFERROR(VLOOKUP(H2&"|"&H3,$A$2:$D$100,4,FALSE),"일치 항목 없음")을 넣습니다. H2가 ‘USB-C 케이블’, H3가 ‘2m’라면 결과는 7,900입니다. A열이 검색 범위의 첫 번째 열이고, 가격이 있는 D열은 A열부터 세었을 때 네 번째 열이라 숫자 4를 씁니다.

마지막 인수 FALSE는 정확히 같은 값을 찾겠다는 뜻입니다. 이를 생략하면 VLOOKUP은 기본적으로 유사 일치를 사용하므로, 정렬 상태에 따라 엉뚱한 가격이 나올 수 있습니다. 달러 기호를 붙인 $A$2:$D$100은 수식을 다른 셀로 복사해도 조회 범위가 움직이지 않게 고정합니다. 실제 데이터가 100행을 넘는다면 범위도 함께 늘려야 합니다.

보조 열을 추가할 수 없다면

원본 파일의 열 구성을 건드리기 어렵고 XLOOKUP을 쓸 수 있다면 H4에 =XLOOKUP(1,($B$2:$B$100=H2)*($C$2:$C$100=H3),$D$2:$D$100,"일치 항목 없음")을 입력할 수 있습니다. B열에서 상품명이 같으면 TRUE, C열에서 옵션이 같으면 TRUE가 됩니다. 두 비교 결과를 곱하면 두 조건이 모두 맞는 행만 1이 되고, 그 행의 D열 값을 돌려줍니다.

XLOOKUP은 마이크로소프트 365와 엑셀 2021·2024 등에서 사용할 수 있지만, 엑셀 2016·2019에서는 사용할 수 없습니다. 여러 사람이 파일을 주고받는다면 상대방의 엑셀 버전까지 확인하는 편이 좋습니다. 버전이 제각각인 업무 파일에는 보조 열을 둔 VLOOKUP 방식이 더 편할 때가 많습니다.

결과가 이상할 때 확인할 곳

첫 번째 값만 나온다면: 상품명과 옵션의 조합이 여러 행에 중복되어 있는지 확인합니다. VLOOKUP과 위 XLOOKUP 수식은 처음 찾은 행의 값 하나를 반환합니다.
‘일치 항목 없음’이 뜬다면: H2·H3의 값과 원본 데이터에 앞뒤 공백이 있는지 확인합니다. 숫자로 저장된 값과 문자로 저장된 값도 서로 다를 수 있습니다.
다른 열의 값이 나온다면: VLOOKUP 수식의 열 번호를 다시 셉니다. 조회 범위가 A열에서 시작할 때 D열은 4이고, 범위 시작 열을 바꾸면 이 번호도 달라집니다.
새 행만 빠진다면: 수식의 조회 범위와 A열 보조 수식이 새 행까지 채워졌는지 확인합니다.

IFERROR는 찾지 못한 경우를 보기 쉬운 문구로 바꿔주지만, 왜 못 찾았는지까지 알려주지는 않습니다. 수식을 만든 직후에는 IFERROR를 잠시 빼고 원래 오류를 확인하면 원인을 찾기 쉽습니다.

저는 파일을 다른 사람과 함께 쓸 때는 보조 열에 조건을 합치는 쪽을 자주 택합니다. 검색 기준이 셀에 그대로 보여서, 나중에 가격이 다르게 나왔을 때 어느 값으로 찾았는지 확인하기 편하거든요.