엑셀 / 함수 / VLOOKUP, HLOOKUP, XLOOKUP

VLOOKUP은 범위의 첫 열에서 값을 찾아 오른쪽 열을, HLOOKUP은 첫 행에서 찾아 아래 행을 반환합니다. XLOOKUP은 검색 범위와 반환 범위를 따로 지정하여 왼쪽·오른쪽과 가로·세로 조회에 사용할 수 있습니다. VLOOKUP·HLOOKUP의 정확한 일치는 FALSE를 지정해야 하며 XLOOKUP은 정확한 일치가 기본입니다.

VLOOKUP으로 세로 조회

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
=VLOOKUP(E2,$A$2:$C$16,2,FALSE)

E2의 값을 A2:A16에서 찾고 같은 행의 두 번째 열 B열 값을 반환하는 예입니다. 범위 첫 열이 검색 대상이므로 원하는 열이 범위의 맨 왼쪽인지 확인합니다.

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

반환 열 번호를 3으로 바꾸어 C열의 가격을 가져온 예입니다. 열 번호는 시트 전체의 열 번호가 아니라 지정한 table_array 안의 순서입니다.

정확히 일치하는 값이 없으면 #N/A 오류가 납니다. 숫자와 텍스트의 형식 차이, 앞뒤 공백과 누락된 검색 범위를 먼저 확인합니다.

range_lookup을 생략하거나 TRUE로 지정하면 근사 일치입니다. 이때 검색 첫 열의 오름차순 정렬 등 조건이 필요하므로 제품 코드처럼 정확한 일치가 필요한 조회에는 FALSE를 명시하세요. 열 삽입·삭제로 반환 열 번호가 바뀌는지도 확인합니다.

입력 값을 목록으로 제한하기

1. 조회 값을 입력할 셀을 선택하고 [데이터] → [데이터 유효성 검사]를 엽니다.

2. [제한 대상]을 [목록]으로 정하고 존재하는 코드 목록을 원본으로 지정합니다. 목록 범위에 제목이나 빈 셀을 넣지 않도록 확인합니다.

드롭다운에서 값을 선택해 연결된 이름·가격이 표시되는 예입니다. 목록 사용은 입력 실수를 줄이는 방법이지 모든 복사·붙여넣기 오류를 차단하는 보장은 아닙니다.

HLOOKUP으로 가로 조회

=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
=HLOOKUP(B5,$B$1:$I$3,2,FALSE)

첫 행 B1:I1에서 B5의 값을 찾아 범위의 두 번째 행 값을 반환한 예입니다. VLOOKUP의 열 번호 대신 HLOOKUP에서는 행 번호를 지정합니다.

XLOOKUP의 기본 구문

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

검색 범위와 반환 범위의 대응 길이를 맞춥니다. if_not_found에는 미일치 안내를 넣을 수 있고 match_mode는 기본 0인 정확한 일치입니다. -1·1은 각각 작은 쪽·큰 쪽 근사 일치, 2는 와일드카드 일치입니다. search_mode는 기본 1로 앞에서부터 찾으며 -1은 뒤에서부터 찾습니다. 이진 검색 옵션은 정렬 조건을 지켜야 합니다.

=XLOOKUP(E2,A2:A16,B2:B16,"찾는 값 없음")

E2를 A열에서 찾아 B열 이름을 반환하는 XLOOKUP 예입니다. 반환 범위가 검색 열의 왼쪽에 있어도 지정할 수 있습니다.

=XLOOKUP(E2,A2:A16,B2:C16)

이름과 가격 두 열을 함께 반환한 예입니다. 결과가 퍼질 공간에 다른 값이나 병합 셀이 있으면 #SPILL! 오류가 날 수 있습니다.

=XLOOKUP(B5,B1:I1,B2:I2)

가로 검색 범위에 XLOOKUP을 적용한 예입니다. 함수 이름을 HLOOKUP으로 바꾸지 않아도 됩니다.

구문과 지원 제품은 Microsoft의 VLOOKUP 함수 설명, Microsoft의 HLOOKUP 함수 설명, Microsoft의 XLOOKUP 함수 설명에서 확인할 수 있습니다.

지원 버전과 중복 값 확인

XLOOKUP은 Excel 2016·2019에서 사용할 수 없습니다. Microsoft 365와 Excel 2021·2024 등 지원 제품을 확인하고, 구형 버전 사용자와 공유할 때는 대체 수식이 필요한지 검토하세요.

기본적인 조회는 중복 코드가 있으면 첫 일치 결과를 반환할 수 있습니다. 모든 행을 합산하거나 여러 결과를 나열하는 목적은 다르므로 SUMIFS·FILTER 등 해당 기능을 검토합니다. 코드 형식·공백·절대 참조와 찾을 범위를 검증한 뒤 미일치 처리 문구를 추가하세요.