엑셀 VLOOKUP MATCH 함수 - 가로세로 검색 필터 만들기
엑셀 VLOOKUP MATCH 가로세로 필터 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] VLOOKUP MATCH 가로세로 검색 필터완성파일[엑셀고급] VLOOKUP MATCH 가로세로 검색 필터예제파일
이번 강의에서 사용된 기초 함수 목록
개발도구 - 옵션 단추 추가하기
개발도구의 사용자 양식 컨트롤을 사용하면 엑셀로 작성되는 보고서에 다양한 기능을 손쉽게 추가할 수 있습니다. 그 중, 이번 강의에서는 '옵션단추'를 사용하여 VLOOKUP 양방향 검색필터를 제작하는 방법을 하나씩 단계별로 알아보겠습니다.
보고서 작성 시 자주 사용되는 6가지 대표 양식컨트롤에 대한 자세한 사용방법은 이전 강의를 참고해주세요.
- [개발도구] - [삽입] - [양식 컨트롤] - [옵션 단추]를 삽입합니다. 화면 상단에 개발도구가 안 보이실 경우 아래 '개발도구 활성화 방법' 관련 포스트를 참고하여 개발도구를 활성화합니다.
개발도구 - 삽입 - 옵션단추를 삽입합니다. - 시트 위에 드래그하여 옵션단추를 삽입합니다.
시트위에 마우스를 드래그하면 옵션단추가 추가됩니다. - 옵션단추의 텍스트를 '단가'로 변경합니다.
옵션단추의 텍스트를 단가로 변경합니다. - 옵션단추를 복사 한 뒤, 3번 더 붙여넣기 하여 각각의 텍스트를 '판매량', '판매순위', '변동률'로 입력합니다.
옵션단추 3개를 더 추가하여 각각 텍스트를 변경합니다. - 키보드 Shift 키를 누른채로 마우스 '우클릭'하여 옵션단추를 모두 선택합니다. [도형서식] - [맞춤] - [가로 간격을 동일하게] 및 [중간 맞춤]을 선택하여 옵션 단추를 보기 좋게 정렬합니다.
옵션단추를 모두 선태한 뒤, 보기 좋게 정렬합니다. - 옵션단추 중 하나를 선택한 뒤, 우클릭하여 '컨트롤 서식'으로 이동합니다. 이후 '셀 연결'을 선택한 뒤, 시트의 [R2]셀을 선택합니다. '확인'을 눌러 마무리합니다.
옵션단추를 R2셀과 연결합니다. - 옵션단추 삽입이 완료되었습니다. 이제 옵션단추를 클릭하면 옵션단추의 순서에 따라 연결된 셀의 값이 옵션단추의 순번으로 변경됩니다.
옵션단추를 선택하면 R2셀의 값이 옵션단추의 순번으로 변경됩니다.
INDEX 함수로 옵션단추 연동하기
옵션단추는 결과값으로 순번을 반환합니다. 따라서 해당 순번을 참조하여 INDEX 함수를 사용하면, 특정 범위내 해당 순번에 대한 다른 결과값으로 변환할 수 있습니다. INDEX 함수에 대한 자세한 설명은 아래 관련 포스트를 참고하세요.
- 예제파일의 [R7]셀을 선택합니다. 이후 R7셀에 아래 수식을 입력합니다.
=INDEX(G3:J3,R2)
'구분'셀에 INDEX 함수를 입력합니다. - 이제 옵션단추를 클릭하면 선택 된 옵션단추의 텍스트가 R7셀에 동일하게 출력됩니다.
옵션단추를 클릭하면 옵션단추의 텍스트가 출력됩니다.
엑셀 VLOOKUP MATCH 가로세로 검색필터 만들기
VLOOKUP 함수와 MATCH 함수를 동시에 응용하면 기존 세로방향으로만 검색가능하던 VLOOKUP 함수의 한계를 뛰어넘어 가로/세로 양방향 검색이 가능한 공식을 작성할 수 있습니다.
VLOOKUP + MATCH 공식의 동작원리는 아래 관련포스트에서 단계별로 자세히 설명드렸습니다.
- 예제파일의 S5셀(제품코드)을 선택한 뒤, 좌측 표의 제품코드 중 하나를 입력합니다.
제품코드를 입력합니다. - S6셀(제품명)에는 기본 VLOOKUP 함수를 사용하여 입력한 제품코드에 대한 제품명을 출력합니다.
=VLOOKUP(S5,B3:P59,2,0)
VLOOKUP 함수로 제품코드에 대한 제품명을 불러옵니다. - S7셀(구분)에는 아래 VLOOKUP / MATCH 가로세로 검색 공식을 입력합니다. VLOOKUP/MATCH 함수의 동작 원리는 영상강의에서 자세히 설명해드렸습니다.
=VLOOKUP(S5,B3:P59,MATCH(R7,B3:P3,0),0)
VLOOKUP MATCH 가로세로 검색 공식을 입력합니다. - 엑셀 VLOOKUP MATCH 가로세로 필터 만들기가 완료되었습니다. 이제 옵션단추를 클릭하면 선택된 항목에 대한 값으로 자동갱신됩니다.
엑셀 VLOOKUP MATCH 가로세로 검색 필터가 완성되었습니다.
