VLOOKUP 조건 2개로 찾기·다른 시트에서 찾기 — 보조 열로 푸는 방법

조건이 2개이면 왜 안 되나

마이크로소프트 문서에 따르면 VLOOKUP이 찾는 값은 범위의 첫 번째 열에 있어야 합니다. 즉 한 번에 한 열만 비교할 수 있습니다. 같은 품목이 지점마다 다른 단가를 가질 때 「품목」 열만 보고 찾으면 (마지막 인수를 FALSE로 정확히 일치 검색할 때) 항상 맨 위에서 처음 발견한 행의 값을 돌려줍니다. 그래서 두 조건(지점·품목)을 하나의 열 값으로 합쳐 주면 됩니다.

보조 열로 조건 2개 찾기

시트 이름이 「단가표」이고 아래처럼 정리되어 있다고 가정합니다. 원래 표는 지점(B)·품목(C)·단가(D)이고, 맨 앞에 A열을 하나 끼워 넣어 보조 열로 씁니다. 보조 열이 표 범위의 첫 열이어야 하기 때문입니다.

A2 셀에 아래 수식을 넣고 A6까지 복사합니다. &는 두 값을 글자로 이어 붙이는 연산자입니다.

=B2&"|"&C2

이제 조회하는 쪽 시트에서 지점을 F2, 품목을 G2에 입력해 두었다면 단가는 다음처럼 찾습니다. 찾는값도 보조 열과 똑같은 방식으로 이어 붙여야 합니다.

=VLOOKUP(F2&"|"&G2,단가표!$A$2:$D$6,4,FALSE)

표가 조회하는 쪽과 다른 시트(「단가표」)에 있으므로 범위 앞에 단가표!를 붙였습니다. 표가 같은 시트에 있다면 $A$2:$D$6만 쓰면 됩니다.

마지막 줄은 표에 없는 조합이라 #N/A가 납니다. 열 번호 4는 범위 A2:D6 안에서 단가가 네 번째 열이기 때문입니다. 마지막 인수 FALSE는 정확히 일치를 뜻합니다. 여기서는 정렬 여부와 관계없이 존재하는 조합만 정확히 찾아야 하므로 FALSE를 씁니다. 마지막 인수를 TRUE로 하거나 생략하면 대략 일치라서 정렬이 필요하고, 없는 조합이어도 오류 대신 다른 행의 단가를 돌려줄 수 있습니다(대략 일치는 정렬된 첫 열에서 찾는 값보다 작거나 같은 값 중 가장 큰 값의 행을 고르며, 정렬되지 않았으면 결과를 예측할 수 없습니다).

보조 열 값(지점+품목 조합)은 표 안에서 하나뿐이어야 합니다. 같은 키가 여러 번 있으면 VLOOKUP은 처음 발견한 행만 돌려주므로, 중복 조합에 다른 단가가 적혀 있으면 의도하지 않은 값이 나옵니다.

구분 문자를 넣는 이유

두 값을 구분 문자 없이 그냥 붙이면 서로 다른 조합이 같은 글자가 될 수 있습니다. 예를 들어 코드 1과 23을 붙이면 123, 12와 3을 붙여도 123이 됩니다. 사이에 | 같은 문자를 넣으면 1|23과 12|3으로 구분됩니다. 데이터에 나오지 않을 문자를 고르세요. 다만 FALSE 정확히 일치 검색에서도 찾는 값에 *나 ?가 들어 있으면 엑셀이 와일드카드로 해석할 수 있습니다. 지점·품목 이름에 이런 문자가 올 수 있다면 구분 문자로 쓰지 말고, 값 안의 문자는 앞에 ~를 붙여(~*, ~?) 찾아야 합니다. 셀 값을 찾는값으로 쓸 때는 조회값 쪽을 SUBSTITUTE로 바꿔 주면 됩니다. 값에 ~가 들어올 수 있으면 ~를 가장 먼저 ~~로 바꿉니다.

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2&"|"&G2,"~","~~"),"*","~*"),"?","~?"),단가표!$A$2:$D$6,4,FALSE)

다른 시트의 표에서 찾기

마이크로소프트 문서의 예시처럼 표 범위 앞에 시트 이름과 느낌표를 붙이면 다른 시트의 표를 쓸 수 있습니다.

=VLOOKUP(A2,'Client Details'!A:F,3,FALSE)

위 예에서 보듯이 시트 이름에 공백이 있으면 작은따옴표로 감쌉니다. 앞의 보조 열 예제를 다른 시트(「주문」 시트)에서 쓰려면 다음처럼 적습니다.

=VLOOKUP(A2&"|"&B2,단가표!$A$2:$D$6,4,FALSE)

범위를 마우스로 고르면 엑셀이 시트 이름을 알아서 붙여 줍니다. 아래로 복사할 때 범위가 밀리지 않도록 $로 고정하는 이유는 VLOOKUP 사용법 글에 정리해 두었습니다.

Excel 2021·2024 또는 Microsoft 365라면: XLOOKUP

마이크로소프트는 VLOOKUP의 개선판으로 XLOOKUP을 안내합니다. 형식은 =XLOOKUP(찾는값, 찾을 범위, 돌려줄 범위, [못 찾았을 때], [일치 방식], [검색 방식])이며, 못 찾았을 때 표시할 글자를 직접 정할 수 있고 생략하면 #N/A가 나옵니다. 다만 문서에 따르면 XLOOKUP은 Excel 2016과 Excel 2019에서는 쓸 수 없고, Excel 2021 · 2024 · Microsoft 365에서 쓸 수 있습니다. 파일을 주고받는 상대의 버전이 다양하면 보조 열 + VLOOKUP이 더 널리 통하는 방법입니다.

잘 안 될 때 확인할 것

• 보조 열이 범위의 맨 왼쪽인지 — 표 범위가 보조 열에서 시작하지 않으면 못 찾습니다.
• 구분 문자·공백이 양쪽에서 똑같은지 — 한쪽에 공백이 하나 더 있으면 #N/A가 납니다.
• 열 번호 — 보조 열을 끼워 넣으면 기존 열 번호가 1씩 밀립니다. 수식을 고치지 않으면 엉뚱한 열 값이 나옵니다.
• 숫자와 숫자 모양의 글자가 섞였는지 — 한쪽은 숫자 123, 다른 쪽은 글자 "123"이면 눈으로는 같아 보여도 못 찾습니다. (보조 열과 찾는값 모두 &로 이어 붙이면 단순한 123과 "123"은 둘 다 글자 123이 되어 맞습니다. 그러나 앞자리 0이 있는 코드(00123), 날짜, 사용자 지정 표시 형식은 화면에는 같게 보여도 이어 붙일 때 셀의 실제 값이 쓰입니다. 예를 들어 숫자 123을 00000 형식으로 표시한 셀은 이어 붙이면 123이 되고, 글자 "00123"은 00123이 되어 서로 다른 키가 됩니다. 이런 값은 양쪽 모두 TEXT 함수 등으로 형식을 통일해서 이어 붙이세요.)
• 키 길이 — 찾는값이 255자를 넘으면 #VALUE!가 납니다. 긴 설명문이나 주소를 이어 붙인다면 주의하세요.
• 대소문자 — VLOOKUP은 영문 대소문자를 구분하지 않으므로 SEOUL|PEN과 seoul|pen은 같은 키로 취급됩니다. 대소문자를 다른 키로 관리해야 하는 자료에는 맞지 않습니다.
• 마지막 인수 — 생략하면 대략 일치로 동작합니다. 꼭 FALSE를 씁니다.
• 그 밖의 #N/A 원인은 VLOOKUP이 #N/A만 나올 때 글을 참고하세요.

이 글의 보조 열 방식은 「첫 열에서만 찾는다」는 VLOOKUP의 규칙을 이용한 응용입니다. 마이크로소프트 VLOOKUP 문서가 이 방법을 따로 소개하지는 않으며, 예제 결과는 같은 규칙으로 계산해 확인했습니다. 쓰는 파일에서 한두 개 조합을 직접 대조해 보세요.