가로세로 검색 옵션 필터 만들기

by 오빠두엑셀

학습시간 10분
난이도 초급
작성일 2020.08.05

VLOOKUP + MATCH 함수로 가로세로 검색이 가능한 옵션필터를 만드는 방법!

이 강의에서는 엑셀 양식 컨트롤의 옵션 단추와 VLOOKUP, MATCH 함수를 결합해 가로세로 양방향 검색이 가능한 동적 필터를 제작하는 방법을 다룹니다. 옵션 단추의 순번을 INDEX 함수로 텍스트 헤더에 연결하고, 그 결과를 MATCH의 인수로 넘겨 VLOOKUP의 열 번호를 동적으로 바꾸는 흐름을 따라가며, 실무 보고서에 곧바로 활용할 수 있는 검색 필터를 단계별로 완성합니다.

DOWNLOADS

실습자료를 준비했어요

수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇

실습파일.xlsx

로그인 후 다운로드

개발도구 - 옵션 단추 추가하기

개발도구의 사용자 양식 컨트롤을 사용하면 엑셀 보고서에 다양한 인터랙션 기능을 손쉽게 추가할 수 있습니다. 그중 이번 강의에서는 옵션 단추(Option Button)를 활용하여 VLOOKUP 가로세로 양방향 검색 필터를 단계별로 제작하는 방법을 살펴보겠습니다.

보고서 작성 시 자주 사용되는 6가지 대표 양식 컨트롤의 자세한 사용 방법은 이전 강의에서 정리해두었으니 함께 참고해주세요.

[개발도구] - [삽입] - [양식 컨트롤] - [옵션 단추]를 차례로 선택하여 삽입합니다. 리본 메뉴 상단에 개발도구 탭이 보이지 않을 경우, 아래 관련 포스트를 참고하여 개발도구 탭을 먼저 활성화합니다.
시트 위에 마우스로 드래그하여 옵션 단추를 삽입합니다.
삽입된 옵션 단추의 텍스트를 '단가'로 변경합니다.
옵션 단추를 복사한 뒤 3번 더 붙여넣기 하여 각각의 텍스트를 '판매량', '판매순위', '변동률'로 입력합니다.
키보드 Shift 키를 누른 채로 마우스 우클릭하여 옵션 단추 4개를 모두 선택합니다. 이후 [도형 서식] - [맞춤] - [가로 간격을 동일하게] 와 [중간 맞춤]을 차례로 적용하여 옵션 단추를 가지런히 정렬합니다.
옵션 단추 중 하나를 우클릭한 뒤 '컨트롤 서식'으로 이동합니다. 이어서 '셀 연결' 항목을 선택하고 시트의 [R2]셀을 지정한 다음 '확인' 버튼을 눌러 마무리합니다. 옵션 단추 그룹 중 하나만 셀 연결을 지정해도 같은 그룹에 속한 모든 단추가 동일한 셀에 결과값을 반환합니다.
옵션 단추 삽입이 완료되었습니다. 이제 각 옵션 단추를 클릭하면 단추의 배치 순서에 따라 연결된 [R2]셀의 값이 1, 2, 3, 4 순번으로 변경됩니다.

INDEX 함수로 옵션단추 연동하기

옵션 단추의 결과값은 클릭한 단추의 순번이므로, 이 순번을 INDEX 함수의 인수로 활용하면 특정 범위에서 해당 위치의 값을 자유롭게 꺼내올 수 있습니다. 옵션 단추의 순번을 텍스트로 다시 변환하는 데 INDEX 함수가 가장 손쉬운 방법입니다. INDEX 함수의 자세한 동작 원리는 아래 관련 포스트에서 단계별로 정리해두었습니다.

예제파일의 [R7]셀을 선택한 뒤 아래 수식을 입력합니다. 수식은 G3:J3 범위(단가, 판매량, 판매순위, 변동률 헤더)에서 [R2]셀에 저장된 옵션 단추 순번에 해당하는 값을 반환합니다.
=INDEX(G3:J3,R2)
이제 옵션 단추를 클릭하면 선택된 단추의 텍스트가 [R7]셀에 동일하게 출력됩니다. 이 결과값이 다음 단계에서 가로 방향 검색의 기준이 됩니다.

엑셀 VLOOKUP MATCH 가로세로 검색필터 만들기

VLOOKUP 함수와 MATCH 함수를 함께 응용하면 세로 방향 조회만 가능하던 VLOOKUP의 한계를 넘어 가로/세로 양방향 검색이 가능한 동적 검색 공식을 완성할 수 있습니다.

VLOOKUP + MATCH 공식의 동작 원리는 아래 관련 포스트에서 단계별로 자세히 설명해두었으니 본 강의와 함께 참고하시면 이해가 한결 빨라집니다.

예제파일의 [S5]셀(제품코드 입력칸)을 선택한 뒤, 좌측 표에 있는 제품코드 중 하나를 직접 입력합니다.
[S6]셀(제품명)에는 기본 VLOOKUP 함수를 사용해 입력한 제품코드에 해당하는 제품명을 출력합니다. 두 번째 인수의 열 번호 '2'는 표 범위에서 두 번째 열(제품명)을 의미합니다.
=VLOOKUP(S5,B3:P59,2,0)
[S7]셀(구분)에는 아래 VLOOKUP/MATCH 가로세로 검색 공식을 입력합니다. MATCH 함수가 [R7]셀의 텍스트를 표 머리글(B3:P3) 안에서 찾아 열 번호로 변환하면, VLOOKUP이 이 동적 열 번호를 사용해 해당 열의 값을 반환합니다. 핵심 동작 원리는 영상 강의에서 자세히 다루었습니다.
=VLOOKUP(S5,B3:P59,MATCH(R7,B3:P3,0),0)
엑셀 VLOOKUP MATCH 가로세로 필터 만들기가 완성되었습니다. 이제 옵션 단추를 클릭할 때마다 선택된 항목에 해당하는 값이 [S7]셀에 자동으로 갱신되어 출력됩니다.