일치하는 행의 다른 열 값 찾기

1. 문제

일치하는 행의 값 구하기(문제).xlsx

0.01MB

아래와 같이 H열에 값이 있는데, H16셀과 일치하는 행을 찾아 그 행에 해당하는 G열의 값을 찾으려고 합니다. 이 경우는 63의 왼쪽 값이므로 13이 됩니다.

 

2. 해결 방안 

가. 수식 1

match함수로 일치하는 값, 여기서는 63의 위치를 찾은 다음, 그 위치, 여기서는 8행에 해당하는 값인 13을 index로 찾으면 됩니다.

수식은 =INDEX($G$2:$G$12,MATCH(H16,$H$2:$H$12,0)) 입니다.

match 함수의 결과가 1부터 시작하기때문에 index의 참조 범위도 G2셀부터 시작합니다.

배열 수식이므로 엑셀 버전이 낮다면 Ctrl + Shift + Enter키를 눌러 수식을 입력해야 하며,

그러면 수식 좌우에 중괄호가 추가됩니다.

나. 수식 2

가장 큰 row를 구해서 그 행에 해당하는 G열의 값을 구할 수도 있습니다.

수식은 아래와 같습니다.

=INDEX($G$1:$G$12,AGGREGATE(14,3,1/($H$2:$H$12=H16)*ROW($H$2:$H$12),1))

Aggregate 함수의 구문은

AGGREGATE(function_num, options, array, [k])로서

function_num으로 Large에 해당하는 14를 입력하고,

options는 오류를 무시할 것인지 옵션에 해당하는 숫자로 3이 무난하며,

array는 배열을 정하는 것으로 1/($H$2:$H$12=H16)*ROW($H$2:$H$12)인데,

H2셀에서 H12셀까지 범위에서 H16셀과 일치하면 True, 아니면 False를 반환하므로 1로 나누면 True는 1, False는 #DIV/0!(0으로 나눌 수 없음) 오류가 발생하는데, 여기에 ROW($H$2:$H$12)를 곱하므로 True면 행 수를 반환하고, False인 경우는 여전히 #DIV/0! 오류가 발생합니다.

1/($H$2:$H$12=H16)*ROW($H$2:$H$12)을 마우스로 끌어서 범위를 선택한 후 F9키를 누르면 아래와 같이 8과 #DIV/0! 오류가 반환됩니다.

그리고, 네번째 인수는 서수로서 1이므로 첫번째로 큰 수가 구합니다.

그러면 AGGREGATE(14,3,1/($H$2:$H$12=H16)*ROW($H$2:$H$12),1)의 값은 8이 됩니다.

이제 index 함수로 $G$1:$G$12에서 8행의 값을 구하므로 13이 구해집니다.

 

다. 수식 3

XLookup 함수를 이용할 수도 있는데, 구문은 아래와 같습니다.

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

수식은 =XLOOKUP(H16,$H$2:$H$12,$G$2:$G$12)입니다.대괄호([])사이에 있는 것은 옵션이고, 기본 값이 정확히 일치(match_mode가 0)이므로 줄 필요가 없습니다.

일치하는 행의 값 구하기(답).xlsx

0.01MB