엑셀 같은 값 찾아서 넣기 - egsel gat-eun gabs chaj-aseo neohgi

다음 그림과 같이 상품정보에서 단가를 연도별로 관리한다고 할때 단가를 찾으려면 상품코드에 연도 조건을 추가해서 찾아야 합니다.

이 경우에 VLOOKUP 함수나 INDEX, MATCH 함수를 이용한 단일 조건으로는 값을 찾을 수 없고 INDEX, MATCH 함수와 배열 수식을 조합하면 값을 찾을 수 있습니다.

※ 예제 이해에 필요한 내용은 다음 링크를 참고하세요.
[ VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기 ] - INDEX, MATCH를 이용한 단일 조건으로 값찾기
[ 배열수식 제대로 이해하기 ] - 배열 수식 기초
[ INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 ]
[ MATCH 함수 - 범위에서 값의 위치 찾기 ]

1. 찾는 조건을 TRUE, FALSE 바꾸어서 찾기

찾고자 하는 값을 배열과 비교하여 맞으면 TRUE, 아니면 FALSE로 바꾸어서 찾는 방법입니다.

이 방법은 배열수식, INDEX, MATCH 함수 등 사전에 알아야 할 것이 많지만 알아 두면 복잡한 조건으로 값을 찾을 때 많은 도움이 됩니다.

판매 연도, 상품코드에 해당하는 단가를 찾기 위해 [E19] 셀에 다음 수식을 입력합니다.

=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))

배열 수식이므로 수식 입력 후 [Enter] 대신에 [Ctrl+Shift+Enter]를 눌러야 합니다.
* Microsoft 365, 엑셀 2021 이상 버전에선 [Enter]만 눌러도 됩니다.
수식이 제대로 입력되면 배열수식을 의미하는 중괄호가 수식 양쪽에 붙어 있습니다.
{=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))}

수식이 제대로 입력되었다면 다음과 같이 상품코드가 'A002'이고 적용연도가 2020년인 단가 35,000원을 가져옵니다.

배열수식을 이용해서 값을 가져왔는데 이 복잡한 수식이 어떻게 작동되는지 확인해보겠습니다.

INDEX 함수안에 MATCH 함수가 포함되어 있으므로 MATCH 함수가 먼저 실행됩니다.
=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14=C19)*($D$6:$D$14=B19),0))

위의 수식에서 찾는 조건을 값으로 바꾸면 다음과 같이 됩니다.
=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14="A002")*($D$6:$D$14="2020"),0))

아래와 같이 수식의 파란색 부분은 서로의 결과를 곱하는 것입니다.
($B$6:$B$14="A002")*($D$6:$D$14="2020")

곱하기 전에 앞 부분과 뒷 부분을 떼어서 그림으로 표현하면 다음과 같습니다.
($B$6:$B$14="A002") => 상품코드가 'A002'이면 TRUE로 바뀌고 아니면 FALSE로 바뀝니다.
($D$6:$D$14="2020") => 적용연도가 2020년이면 TRUE로 바뀌고 아니면 FALSE로 바뀝니다.

상품코드와 적용연도 조건을 비교해서 TRUE, FALSE로 바꾸고 서로를 곱하면 결과는 다음과 같습니다.
엑셀에서 TRUE는 1, FALSE는 0으로 인식합니다.

($B$6:$B$14="A002")*($D$6:$D$14="2020")

결과적으로 상품코드가 'A002'이고 적용연도가 2020년인 것을 만족하는 것은 4번째 행이 됩니다.

위의 결과를 배열로 표현하면 {0;0;0;1;0;0;0;0;0} 와 동일합니다.

따라서 이 수식은
=INDEX($E$6:$E$14,MATCH(1,($B$6:$B$14="A002")*($D$6:$D$14="2020"),0))

다음과 같이 표현할 수 있고,
=INDEX($E$6:$E$14,MATCH(1,{0;0;0;1;0;0;0;0;0},0))

INDEX 함수 안의 MATCH 함수를 해석해 보면 1이라는 값을 {0;0;0;1;0;0;0;0;0} 범위에서 몇 번째 있는지 찾는데 4번째 행에 있으므로 4를 반환합니다.
MATCH(1,{0;0;0;1;0;0;0;0;0},0) => 4

MATCH 함수의 결과 4를 받아서 수식은 다음과 같이 바뀝니다.
=INDEX($E$6:$E$14, 4)

최종적으로 INDEX 함수는 단가 범위 [$E$6:$E$14]의 4번째 행의 35,000원을 구해줍니다.

2. & 기호(결합연산자)로 묶어서 찾기

앞에서 살펴본 TRUE, FALSE로 바꾸는 방식보다는 간단하고 이해하기 쉬운 방식입니다.

판매 연도, 상품코드에 해당하는 단가를 찾기 위해 [E28] 셀에 다음 수식을 입력합니다.
=INDEX($E$6:$E$14,MATCH(C28&B28,$B$6:$B$14&$D$6:$D$14,0))

배열 수식이므로 수식 입력 후 [Enter] 대신에 [Ctrl+Shift+Enter]를 눌러야 합니다.
* Microsoft 365, 엑셀 2021 이상 버전에선 [Enter]만 눌러도 됩니다.
수식이 제대로 입력되면 배열수식을 의미하는 중괄호가 수식 양쪽에 붙어 있습니다.
{=INDEX($E$6:$E$14,MATCH(C28&B28,$B$6:$B$14&$D$6:$D$14,0))}

수식이 제대로 입력되었다면 다음과 같이 상품코드가 'A002'이고 적용연도가 2020년인 단가 35,000원을 가져옵니다.

배열수식을 이용해서 값을 가져왔는데 수식이 어떻게 작동되는지 확인해보겠습니다.

이 방식은 '단일 조건으로 찾기'와 같은 방식으로 이해할 수 있습니다.

차이점은 다음과 같이 상품코드만 가지고 비교하던 것을
"A002"

상품코드에 '&' 기호로 연도를  묶어서 비교하는 것만 차이가 있을 뿐 기본 개념이 동일합니다.
"A002"&2020 => 'A0022020'

따라서 최초 입력된 다음 수식은
=INDEX($E$6:$E$14,MATCH(C28&B28,$B$6:$B$14&$D$6:$D$14,0))

다음과 같이 표현할 수 있습니다.
=INDEX($E$6:$E$14,MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0))

INDEX 함수안에 MATCH 함수가 포함되어 있으므로 MATCH 함수가 먼저 실행됩니다.
MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0)

위 수식의 $B$6:$B$14&$D$6:$D$14부분을 그림으로 표현하면 다음과 같이 내부적으로 2개의 범위가 합쳐져서

아래와 같이 배열이 만들어 집니다.

이제 MATCH 함수에서 'A0022020' 라는 값을 아래 그림의 배열에서 찾으면
4번째 행에 있으므로 4를 반환합니다.
MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0) => 4

결과적으로 상품코드가 'A002'이고 적용연도가 2020년인 것을 만족하는 것은 4번째 행이 됩니다.

따라서 다음 수식에서
=INDEX($E$6:$E$14,MATCH("A0022020",$B$6:$B$14&$D$6:$D$14,0))

MATCH 함수의 결과 4를 받으면 수식은 다음과 같이 바뀝니다.
=INDEX($E$6:$E$14, 4)

최종적으로 INDEX 함수는 단가 범위 [$E$6:$E$14]의 4번째 행의 35,000원을 구해줍니다.

[ 찾기 및 참조영역 함수 목록 ]