복수 조건으로 vlookup으로 안될때는 index와 match로 해결

안녕하세요. 

 

전 글에서는 vlookup을 할 때 복수조건이라면 하나의 조건으로 만들 후에 vlookup을 사용하기를 했었죠.

 

그것은 조금 어렵게 느껴지는 index와 match를 피하고 싶은 마음을 갖고 계신분들에게는 최고라고 할 수 있지 않을까 생각합니다. 

 

이번에는 딱 한번만 이해하면 너무나도 쉬워지는!

복수조건 때문에 vlookup이 안될 때 사용할 수 있는!

vlookup을 해야 하는데 찾을 값이 왼쪽이 아닌 오른쪽에 있을때!

 

그럴때는  index와 match로 한방에 해결할 수 있습니다.

 

이게 뭐야? 라고 생각하시는 분들을 위해 잠시 설명드리자면...

 

index는 범위를 지정하고 지정된 범위에서 가로 몇번째, 세로 몇번째에 무엇이 있니? 라고 물어볼 수 있는 함수입니다.

match는 아래로 또는 오른쪽으로 찾고 싶은 값이 몇번째 있니? 라고 물어볼 수 있는 함수입니다.

 

참 쉽죠윙?

 

복수 조건, vlookup으로 안될때 index와 match 함수로 사용.

 

1. 아래 그림은 vlookup으로 불가능합니다. 그 이유는 두가지입니다.

 

  - ① 찾아야할 것이 ② 범위보다 왼쪽에 있다.

  - ② 조건이 2개 있다.

[vlookup이 안되는 데이터 형식]

 

① 찾아야할 것이 왼쪽에 있어서 vlookup이 안된다면!?

 

1. index와 match의 조합으로 해결합니다.

 

[단일 조건의 index와 match 조합]

 

 - index함수는 =index(범위, 세로 몇 번째, 가로 몇번째) 라고 사용합니다. 따라서 세로 몇 번째인지를 숫자로 표현하여 불러올 위치를 선정하고 가져오는 것입니다. 반환하는 값은 셀에 있는 데이터입니다.

 

 - match함수는 =match(찾을값, 범위, 정확한일치 또는 유사일치)입니다. 반환하는 값은 위치가 몇번째 있는지 숫자를 반환합니다.

 

 - 즉! 논리로 표현하자면 ①에서 찾는데, 찾을 곳의 세로는 match로 찾은 숫자만큼 내려가고, 오른쪽은 ④1칸(제자리)에 있는 값을 가져와라! 라고 하는것입니다.

 

 - 세로 match로 찾은 숫자만큼의 논리는 ②I3(바지)를 ③D3~D9까지의 범위의 몇번째인지를 찾는데 정확히 I3(바지)과 똑같은 것을 찾아서 몇번째인지를 알려달라는 것입니다.

 

① 가져올 값이 있는 범위

② 찾을값

③ 찾을값이 있는 범위

④ 정확한일치(0) 또는 보다작음(1) 또는 보다큼(-1)

⑤ 찾는 곳의 가로(모르시면 그냥 한줄 안에서 찾는거니까 1 쓰세요~)

 

2. 조금더 풀어서 그림으로 보여드리자면!

 - match만 딱 보면, ①에 입력되어 있는 함수는 =MATCH(I3,D3:D9,0) 입니다. 반환되는 값은 1입니다.

[match함수의 풀이 예시]

②를 ③에서 찾는데 몇번째에 그 값이 있는지를 말하는 것입니다. ③의 첫번째에 있으니 답은 1입니다.

 

- index는 =index(범위, 세로 몇 번째, 가로 몇번째) 이렇게 쓰기 때문에 ..

[match 함수예시]

 - ①의 범위에서 match로 찾은 1을아래로 내려가고 index함수의 마지막에 있는 숫자인 1만큼 오른쪽에 있는 값을 추출하라는 것입니다.

 - index 함수는 시작지점을 1로 계산하기 때문에 아래한칸, 오른쪽 한칸은 제자리겠네요. 그럼 반환되는 값은 10,000입니다. 

 

 - 따라서 찾아야하는 범위 오른쪽에 조건이 있을때에도 찾아야하는 값을 찾을 수 있겠습니다!

 

② 조건이 복수로 있어서 vlookup이 안된다면!?

이럴때는 index, match, index를 써줘야 합니다. 결론부터 말씀드리면 함수식은 아래와 같습니다.

 

=INDEX($C$3:$C$9,MATCH(I3&J3,INDEX($D$3:$D$9&$E$3:$E$9,),0),1)

 

[복수 조건의 index, match, index]

① 값이 있는 범위

② 찾아야할 조건을 &로 묶기

③ 찾아야할 범위를 ②에서 묶은 순서대로 &로 묶기

④ 찾은값이 찾을범위에 정확하게 일치

⑤ 값이 있는 범위의 1칸

 

※ 주의하실 점은 범위는 반드시 같은 행으로 써야 합니다. C열의 3부터 C열의 9까지를 범위로 잡았다면 무엇이든지 3~9까지로 범위를 잡아야 한다는것입니다~

 

- 논리를 보면

  C3~C9까지에서 찾아야 하는데 C3에서 시작해서 아래로 match와 index(그림 [복수 조건의 index, match, index]의 함수의 빨간줄과 파란줄) 만큼 내려가고, 오른쪽으로는 한칸에 있는 값을 찾아라 입니다.

 

 - match와 index 조합의 논리는 찾을 값은 I3&J3(바지분홍바지)이며, 범위는 D3~D9&E3~D9(종류&세분류)의 몇번째에 있는지 숫자로 보여줘 입니다.

 

이렇게 하면 복수 조건의 값을 찾을 수 있습니다.

 

여기까지 잘 따라오셨으면 좋겠네요!

 

여러분의 성공을 바라며! 저는 이만 뿅~