vlookup 대신 index(match)사용할 것

vlookup과 동일한 기능을 하면서 vlookup이 받는 제약이 없어 훨씬 더 뛰어난 것이 index와 match함수를 이용하는 것이다.

index와 match는 서로 반대되는 기능을 하는데 함께 사용하면 각자의 단점을 보완해주는 식이라고 할 수 있다.

용법은 다음과 같다.

=index($B:$D,match($A1,$C:$C,0),2)

식을 풀이하자면 먼저

match(찾을 값, 검색범위(1차원), [검색유형])

는 찾고자 하는 값의 위치값(행 또는 열에 해당하는 숫자)을 알려주는데 검색범위가 1차원이다. 1차원이라는 뜻은 면적이 아니라 선이라는 뜻이다.

즉, match 검색범위에 면적을 입력할 수는 없고 단지 행 또는 열 중 하나만 입력가능하며, 행을 입력했다면 몇번째 행에 있는지, 열을 입력했다면 몇번째 열에 있는지만 알려준다.

즉, match($A1,$C:$C,0)라는 뜻은 C열에서 A1칸에 있는 (A앞의 $는 행을 고정해주는 역할을 하며 $A는 '행고정'이라는 뜻이다. 쉽게 말해서 식을 복붙해도 A열값은 변하지 않고 $A1, $A2....식으로 뒤의 열값만 바뀌게 된다. ) 값과 같은 값을 찾아서 그 위치가 반환되는데 우리가 찾는 데이터가 들어있는 셀의 위치가 C5라고 가정해보겠다. 그러면 match식의 결과는 5이다. 즉 C열에서 5번째 칸에 찾고자 하는 값이 있으므로 C5의 열의 위치값인 5를 반환한다.

→match에서 주의할 점은 match에서의 범위는 면적(2차원)이 아니라 1차원 배열(행 아니면 열)만 쓸 수 있다는 점이다!

즉, match는 "찾고자 하는 행 또는 열의 위치값"만 반환한다. 즉, 면적에 해당하는 2차원배열(면적범위)를 입력할 수는 없다.

맨 뒤의 [검색유형]은 0으로 두면 된다. (1이 기본값으로 오름차순으로 검색하며 0은 정확히 일치하는 값, -1은 내림차순으로 검색한다. 보통은 그냥 0으로 두면 된다.)

match에서 열의 위치에 해당하는 5를 반환했으니 이 값이 index식에 반환되면 결국

처음의 복잡한 식 =index($B:$D,match($A1,$C:$C,0),2) 은 아래와 같이 간단하게 변하는데

=index($B:$D,5,2)

이라는 식이 되는데 풀이하자면 B~D열에서 5번째 행, 2번째 열에 들어있는 값을 반환하라는 뜻이다.

index식의 문법은 다음과 같다.

index(참조영역, 이동할[행]의 수, 이동할[열]의 수)

즉, 위의 식을 풀이하면 B열에서 아래로 5칸, 옆으로 2칸 이동한 위치에 있는 셀을 찾아 그 칸에 입력된 값을 반환해준다.

*여기서 주의할 점은 '1칸 이동'은 사실상 이동한 게 아니라고 보며(index함수에서 이동할 행 또는 열의 수는 '정지'상태가 '1'이다. 즉, 이동하지 않으면 '1'임. 제자리 위치가 1이라는 셈이다.) 결국 =index($B:$D,5,2)식에서 반환되는 값은 C5에 있는 값이다.☞참조영역 B~D열에서 아래로 5칸(5행)이동후 옆으로 2칸(C열) 이동하므로 돌아오는 값은 C5에 있는 값이 된다.

동영상으로 보여주면서 설명하면 정말 쉬운 개념인데 글로 풀어쓰니 엄청 복잡해보인다. (때로는 백문이 불여일견이다. 또한 백견은 불여일행이고...)

결국 match는 '위치값'만을 반환해주고 index는 match에서 위치값을 받아 특정범위내에서 위치값에 해당하는 셀에 입력되어 있는 값을 반환해준다.

좀더 응용하면 이동할 열의 값도 match함수로 원하는 값만 리턴하면되니 결국
=index(찾을값,match(),match())형태로 자유자재로 응용 가능하다.

예)
=index($A$4:$C$10, match($j$2,$c$3:$c$13,0),match(h4,$b$2:$f$2))

*vlookup응용
-참조영역이 우측에 있는 경우
*참고할 열이 우측에 있어 vlookup을 사용할수 없는 경우 위처럼 index함수와 match함수를 병용해도 되나 우회적으로 {,}표시(배열)를 사용해서 해결가능함.

예를들어

=vlookup(E3, {B2:B, A2:A},2,0)

이라는 식은 E3값을 A2:A~B2:B 사이에서 찾아서 2번째 열값을 돌려줌

사실 오피스365버전을 사용하면 vlookup보다 상위개념인 xlookup 함수를 쓰면 가장 간단하긴 하다.

*index, match 함수 응용

=index(참조영역,match(1,--(조건범위1=조건1)*(조건범위2=조건2),0))

*참조영역: 출력할 값이 들어있는 영역
*조건범위: 조건이 입력된 범위(참조범위와 높이 동일해야함)
*1은 true(참)값에 해당

*두 조건이 모두 참인 경우 해당하는 '행'의 위치값을 반환

https://youtu.be/4_ZZqpGvehw