여러 가지 조건을 만족하는 값을 찾을 때(1) - VLookup, Index+Match, XLookup함수

1. VLookup 함수의 한계

VLookup 함수의 구문은

VLOOKUP(찾을 값, 표 범위, 몇 번째 컬럼, [유사일치여부])

인데, 네번째 인수는 선택적 인수이지만 생략하면 유사값을 찾아 반환하므로 정확한 값을 찾도록 0을 입력하거나 False를 선택해야 합니다.

 

여러 가지 조건 검색.xlsx

0.01MB

 

 

 

아래와 같이 나라별, 연도별 인구수를 구하고자 할 때 나라와 연도 2가지 조건을 만족해야 하는데 

 

VLookup함수로는 에러가 발생해서

C2셀의 수식 : =VLOOKUP(A2&B2,$F$2:$F$7&$G$2:$G$7&$H$2:$H$7,3,0)

C3셀의 수식 : =VLOOKUP(A3&B3,$F$2:$H$7,3,0)

 

2. Index + Match 함수

Index와 Match 함수를 결합해서 구해야 하며, 365버전이 아니라면 Shift+Ctrl+Enter키를 눌러서 수식을 입력해야 합니다.

C4셀의 수식 : =INDEX($H$2:$H$7,MATCH(A4&B4,$F$2:$F$7&$G$2:$G$7,0))

 

index 함수의 구문은 INDEX(reference, row_num, [column_num], [area_num])인데,

reference는 참조 영역이고, row_num은 행 번호이고, 열 번호와 영역 번호는 옵션입니다.

위 워크시트의 경우 인구수는 H2셀에서 H7셀에 있고, 행 번호를 알아야 하는데,

 

행 번호는 match 함수를 이용하는데,

구문은 MATCH(lookup_value, lookup_array, [match_type]) 입니다.

lookup_value는 찾은 값, lookup_array는 찾을 영역, match_type은 옵션인데 정확히 일치하는 것을 찾을 때는 0입니다.

 

위 수식에서는 두 가지 조건을 만족해야 하므로 &(결합 연산자)를 이용해서 찾고자 하는 값과, 찾을 범위를 열 기준으로 연결했습니다.

 

다시 말해 찾을 범위는 영국에 해당하는 A4셀과 연도에 해당하는 B4셀을 A4&B4라고 입력하고,

찾을 범위는 나라명이 있는 F열과 연도가 있는 G열을 결합하는데,

F2:F7&G2:G7으로 입력하고, 찾을 범위가 고정되어야 하므로 F4키를 눌러 절대참조형식으로 지정했습니다.

 

 

 

3. XLookup 함수의 놀라운 점

XLookup의 함수의 구문은 아래와 같이

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

찾을 값, 찾을 영역(배열), 반환 영역(배열)이 필수 값이고, match_mode는 옵션인데 기본값이 정확히 일치이므로 VLookup함수읙 경우와 달리 생략해도 됩니다.

 

그러나, XLookup함수는 Excel 2021 이상 버전에서만 사용가능한 제약이 있습니다.

 

XLookup함수를 이용해

C5셀에 =XLOOKUP(A5&B5,$F:$F&$G:$G,$H:$H) 이라고 입력하면 에러가 발생하지 않고 일본, 2021년도의 인구수 200을 반환합니다.

 

XLookup하수에서 찾을 값과 찾을 범위는 Match함수와 동일하게

&연산자를 결합해서 입력하는데,

찾을 범위를 열 중 일정 범위인 F2:F7로 지정하지 않고 F:F라고 열 전체를 지정한 점이 다릅니다.

 

여러 가지 조건 검색1(완성).xlsx

0.01MB