Vlookup함수를 대체하는 Index, Match함수 활용.

"행아아빠의 지금은 엑셀 시간" Vlookup함수를 대체하는 Index, Match함수 활용 썸네일

안녕하세요. 행아아빠입니다.

오랜만에 엑셀 내용으로 인사 드리게 됩니다.

그동안 일주일의 희망을 갖고 로또를 구매 하면서 나름의 통계 노하우로 로또와 관련한 내용으로 블로그에 글을 올리긴 했지만, 엑셀 내용으로 블로그 글을 작성하게 되는것이 참 오랜만입니다.

엑셀 내용에 대해서 조금 소홀히 하게 된 이유에 대해서 전부 말씀 드리긴 힘들지만, 앞으로는 엑셀 내용으로 블로그와 YouTube 운영을 조금 더 적극적으로 해보려고 합니다.

질문은 Youtube 채널 댓글, 블로그, 여러분들이 편하신것에 남겨 주시면 해결해 드릴 수 있는 범위 내에서 답변 드려 보도록 하겠습니다.

※ 행아아빠의 지금은 엑셀 시간

Youtube : https://www.youtube.com/channel/UCQlocK7yFlXLZFt-VTxVaAg 또는 https://www.youtube.com/c/행아아빠

▶ 여러분들의 "행아아빠의 지금은 엑셀 시간" Youtube 채널 구독 버튼과 좋아요 클릭이 저에게 큰 힘이 됩니다.

티스토리 : https://hang-a-fafa.tistory.com

네이버 : https://blog.naver.com/ehdls1318

행아아빠의 지금은 엑셀 시간 Youtube 영상

---

오늘 엑셀 내용으로는 Vlookup함수로 결과값을 얻을 수 없는 수식을 대신해서 사용할 수 있는 Index함수와 Match함수의 결합형태로 수식을 작성해서 결과값이 나오도록 수식을 작성해 보려고 합니다.

오늘 내용도 많은 분들에게 도움이 될 수 있기를 바라겠습니다.

 

Q. "Index, match 하다가 머리가 터질것 같습니다. 급여가 자동 계산 될 수 있도록 만들고 있는데 시책이 있으니 동영상보고, 인터넷보고 지금 몇시간을 해도 잘 안되네요. 기존에 만들어 놓은 툴은 직원들의 각 매출과 그에 따른 급여 계산, 총액등을 계산 했으나 구간별로 추가 보너스를 입력하려니 이해가 되질 않습니다."

▶ 질문을 봤을때 질문자분께서 만들고자 하는것이 구간별로 정해져 있는 결과값을 수식으로 작성하고자 하는 의도인것으로 볼 수 있을것 같은데, 질문 내용으로만 봐서는 정확히 어떤 구간에서 어떤 값이 정해져 있는지 이미지를 첨부해 주시긴 했지만, 이해하기 어려워서 몇번의 소통을 거친뒤 작성하고자 하는 수식을 대략이나마 이해 할 수 있었습니다.

 

index match 함수로 수식을 작성하기 위해서 간단하게 매출 구간과 그에 따라 결정되어 있는 결과값을 표로 정리한 이미지.

그에 따라 매출 구간과, 매출 구간에 따라 정해져 있는 결과값을 표로 정리 해보면 위와 같은 이미지처럼 매출 구간에 따라 정해져 있는 50,000원부터 3,500,000원까지로 A1셀부터 C9셀까지로 표 형식으로 정리를 해 봤습니다.

이때 매출을 E2셀에 입력하면 고정수수료 15%는 F2셀에 작성한 수식으로 결과값이 나와야 하고, G2셀에는 시책수수료가 수식에 의해서 계산된 결과값이 자동으로 입력되어야 하는것으로 볼 수 있습니다.

그럼 F2셀에 고정수수료 15%를 계산 하기 위한 수식을 작성해 보면 간단하게 "=매출*15%" 또는 "=매출*0.15"로 어렵지 않게 간단히 작성할 수 있습니다.

그럼 시책수수료 G열의 수식은 A2~C9셀의 표를 참조해서 매출값이 해당하는 구간의 시책수수료를 결과값으로 불러오기 위해서는 Index Match함수를 사용해서 작성 할 수 있습니다.

여기에서 보통은 표에서 결과값을 불러오기 위한 함수로 가장 먼저 떠올릴수 있는 함수는 Vlookup함수일텐데, Vlookup함수 역시 유사한값을 결과값으로 불러오기 위한 수식을 작성할수 있기는 하지만, 직접 작성해서 확인을 해 보면 결과값이 나오기도 하지만, 오류값 역시 나오기도 하게 됩니다.

결론적으로 Vlookup함수로 원하는 결과값을 전부다 불러올 수 없다는 의미입니다.

따라서 Index함수와 Match함수를 사용해서 각 구간에 해당할때 해당 구간의 결과값을 불러오는 수식을 작성해야 합니다.

"=Index(결과값 범위,Match(매출,매출구간의 시작 범위,1))" 수식은 이처럼 작성할 수 있습니다.

실제로 각각에 해당하는 범위와 셀들을 적용해서 수식을 적용해서 작성해 보면 2행 시책수수료 G2셀 수식은 다음과 같이 작성 할 수 있습니다.

→ =INDEX($C$2:$C$9,MATCH(E2,$A$2:$A$9,1))

index함수와 match함수를 결합한 형태로 수식을 작성해서 불러온 결과값

매출은 임의적으로 1,500,000원을 입력해 봤을때 1,500,000원이 해당하는 매출 구간의 시책수수료는 50,000원이라는 결과값이 수식에 의해서 오류 없이 잘 계산 되는것을 확인할 수 있습니다.

따라서 E열에 직접 매출을 작성해 주시고, 고정수수료와 시책수수료에 작성한 수식을 복사해서 사용하시면 되겠습니다.

오늘 내용도 많은 분들께 도움이 될 수 있기를 바라겠습니다.

그럼 오늘 하루도 좋은 하루 되시길 바라겠습니다.