[실전편] 엑셀에서 vlookup으로 찾을 수 없는 중복데이터 Index, Match함수 사용하는 꿀팁?

엑셀 전문 블로그는 아닙니다만, 엑셀을 활용하다 보면, 할 수 없는 일들이 많았습니다. 그래서 찾았던 건 함수였고, 그 함수로만으로도 안되기 때문에 VBA를 공부하기 시작하죠. 그러면서 코딩으로 빠져드는데, 그러다보면 이런생각이 듭니다.

 

내가 왜 컴퓨터 프로그래밍을 포기하고 일반인들과 사는걸까?

 

회의감이 들기 시작하면, 되도록 함수를 쓰지 않고, 뭔가를 구현하려고 애를 쓰기 시작합니다.

 

또 그러다가, 안되는 것들은 도대체 얼마나 업데이트가 되어야 이런게 구현이 될까? 라는 의문을 품고 손수 수동으로 해결하곤 했습니다.

 

또 서론이 길었습니다.

---

[실전편] 엑셀에서 vlookup으로 찾을 수 없는 중복데이터 Index, Match함수 사용하는 꿀팁?

VLOOKUP 함수를 검색해서 들어오신 분들을 위해서 우선 함수의 특성을 알려드리겠습니다.

 

홍길동의 미팅 날짜와 약속은?

이름	날짜	약속
홍길동	11월 25일	8:00
홍길금	11월 27일	9:00
김닐동	11월 24일	8:30

 

 

데이터가 적을 때에는 그냥 눈으로 봐도 찾는 것을 일도 아닙니다.

 

그럼 이럴때는 어떨까요?

 

우진의 약속 날짜와 약속은?

이름	날짜	약속
민준	11월 25일	8:00
서준	11월 27일	9:00
도윤	11월 24일	8:30
예준	11월 25일	9:30
시우	11월 26일	10:30
하준	11월 27일	11:30
주원	11월 28일	12:30
지호	11월 29일	13:30
지후	11월 30일	14:30
준우	12월 01일	15:30
준서	12월 02일	16:30
건우	12월 03일	17:30
도현	12월 04일	18:30
현우	12월 05일	19:30
지훈	12월 06일	20:30
우진	12월 07일	21:30
선우	12월 08일	22:30
서진	12월 09일	23:30

 

찾기 어려우신가요? 이럴 때, Vlookup 함수를 활용하시면 됩니다.

 

함수의 양식은 이렇게 하시기 바랍니다.

=VLOOKUP("우진",$D$11:$F$28,2,0) “우진” - 찾고 싶은 우진의 셀 행 $D$11:$F$28 – 데이터의 첫행부터 마지막 행까지(무조건 $가 붙어야 합니다.) ,2, - 이름을 기준으로 날짜는 2번째 이기 때문에 숫자 2를 적어 넣습니다. 약속시간이라면? 3을 넣으시면 됩니다.

 

위의 칸이 설명이 잘 되었을지 모르겠습니다.

 

=vlookup(“찾고싶은 사람”,데이터 첫행부터 끝행까지 고정, 찾은 사람의 옆의 값 호출, 0)

 

으로 설명하면 좀 쉬우실지 모르겠네요.

 

vlookup은 설명이 되었다면, 중복셀의 경우는 어떨까요?

 

VLOOKUP의 한계가 여기에 있습니다.

중복값의 경우에는 무조건 첫 번째 찾는 값을 호출하게 됩니다.

무조건 이 함수를 활용해서 뭔가를 하고 싶다고 하시면 절대로 안됩니다.

 

그래서 공부했던 함수 INDEX, MATCH 함수입니다.

 

기존의 중복행 관련함수들은 함수만들기를 통해서 했다면, 밑의 방식은 복잡해 보이지만 가져다가 쓰시면 됩니다.

 

	D	E	F
10	이름	날짜	약속
11	민준	11월 29일	8:00
12	서준	11월 30일	9:00
13	도윤	12월 01일	8:30
14	예준	12월 02일	9:30
15	시우	12월 03일	10:30
16	하준	12월 04일	11:30
17	주원	12월 05일	12:30
18	지호	12월 06일	13:30
19	지후	12월 07일	14:30
20	민준	12월 08일	15:30

문제) 민준의 12월08일의 약속시간이 궁금할 때, 어떤 함수를 써야 할까요?

 

index와 match 함수를 활용하셔야 합니다.

원론적이지만, 함수의 기능을 알려드리겠습니다.

 

index함수 – 표나 범위 내에서 값이나 참조영역을 구하는 함수입니다.index(범위,행번호,열번호) match함수 – 배열에서 지정된 순서의 내가 정한 지정된 값에 일치하는 항목의 위치값을 찾습니다.match(찾을값,범위(달러를 무조건 표기), option)

이런 내용이 무슨 상관인가요? 값만 찾으면 되는겁니다.^^

 

바로 답이 나갑니다.

 

=INDEX($F$11:$F$20,MATCH(1,($D$11:$D$20="민준")*($E$11:$E$20=DATE(2022,12,8)),0))

 

이걸 쓰라고 적으신건가요???

 

한번 설명해 드리겠습니다.

=INDEX(찾고자하는 시간 셀,MATCH(1,(찾는 사람의 이름 열="민준")*(찾는 사람의 약속 날짜 열=DATE(2022,12,8)),0))

 

함수서식을 다 치고나서 SHIFT+CTRL+ENTER를 누르시면 됩니다. 이는 배열수식으로 처리하게 하는 기능합니다.

 

배열이 뭔가요?

 

흠...그냥 쓰시면 안될까요?

 

우리가 중학교 때 배운 그 배열이 맞습니다. 강제로 수식을 조정하는 것으로 생각하시면 됩니다.

 

사실, 저기에 에러처리하는 함수까지 넣어주면 금상첨화이겠지만, 그것까지 함수를 넣어드리기는 힘들 것 같습니다.

 

이를 활용하여, 응용할 수 있습니다. 중복되는 값을 무한으로 늘려갈 수 있습니다.

 

조건1, 조건2, 조건3, 조건4를 늘려갈 수 있습니다.

 

동명이인의 약속은 다르기 때문에, 조건이 늘어나더라도 정확한 값을 찾을 수 있습니다.

 

 

사실, 저는 이 함수로 업무의 4~5시간을 절약한 경험이 있습니다.

이 함수의 사용은 정말로 간단하지만, 이 양식을 만드는 데 있어서는 상당한 시간을 이해하는 데에 할애한 적이 있습니다.

이해하실 필요 없습니다. 그냥 코딩에서 클래스를 가져다 쓰듯 그냥 함수를 그대로 가져다 쓰시면 됩니다.

 

 

제 글 읽어주셔서 감사합니다. 오늘도 행복한 하루 되시기 바랍니다.