엑셀의 데이터 유효성 검사(Data Validation) 기능은 입력할 수 있는 데이터의 범위를 설정해 주는 강력한 도구입니다. 이를 통해 잘못된 값의 입력을 방지하거나, 클릭 한 번으로 값을 선택할 수 있는 드롭다운 목록을 만들어 작업 시간을 크게 절약할 수 있습니다. 특히 VLOOKUP 함수를 사용할 때는 조회할 값을 정확하게 지정해야 하는데, 데이터 유효성 검사를 함께 활용하면 훨씬 편리하고 오류 없이 작업을 처리할 수 있습니다.
이 글에서는 엑셀 데이터 유효성 검사에서 VLOOKUP 사용자 지정 수식을 활용하는 실전적인 2가지 방법을 소개합니다.
무료 엑셀 템플릿을 다운로드하여 직접 연습해 보실 수도 있습니다.
데이터 유효성 검사에서 VLOOKUP 수식을 활용하는 2가지 방법
먼저 예제 데이터를 살펴보겠습니다. 아래 데이터는 여러 영업사원의 지역별 판매 실적을 나타냅니다.
1. 드롭다운 목록과 VLOOKUP 함수 조합하기
첫 번째 방법은 데이터 유효성 검사의 드롭다운 목록 기능을 VLOOKUP 함수와 결합하는 것입니다. 이렇게 하면 조회값을 손쉽게 선택하고 해당 데이터를 바로 찾아낼 수 있습니다. 먼저 D11 셀에 드롭다운 목록을 만드는 방법을 알아보고, 이어서 VLOOKUP 함수에 적용해 보겠습니다.
단계별 방법:
- D11 셀을 선택합니다.
- 상단 메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사 > 데이터 유효성 검사를 차례로 클릭합니다.
그러면 곧바로 대화 상자가 열립니다.
- 설정 탭에서 제한 대상 드롭다운 상자에서 목록을 선택합니다.
- 이후 원본 입력 상자 옆의 열기 아이콘을 클릭합니다.
그러면 데이터 범위를 지정할 수 있는 화면으로 전환됩니다.
- 마우스로 드래그하여 영업사원 이름 범위를 선택합니다.
- Enter 키를 누르면 이전 대화 상자로 돌아갑니다.
- 범위가 정상적으로 선택되었다면 확인 버튼을 누릅니다.
이제 선택한 셀 옆에 드롭다운 화살표 아이콘이 생성됩니다. 아이콘을 클릭하면 목록이 나타나며, 여기서 Sam을 선택한 후 VLOOKUP 함수를 적용해 보겠습니다. 이 목록을 이용해 지역과 매출액을 각각 한 번에 조회할 수 있습니다.
- 지역명을 구하려면 D12 셀에 아래 수식을 입력합니다.
=VLOOKUP(D11,B5:D9,2,0)
- Enter 키를 눌러 결과를 확인합니다.
다음으로 D13 셀에 매출액을 조회해 보겠습니다.
- 아래 수식을 입력합니다.
=VLOOKUP(D11,B5:D9,3,0)
- Enter 키를 눌러 출력값을 확인합니다.
이후 다른 영업사원의 지역과 매출액을 조회하고 싶다면, 드롭다운 목록에서 이름만 변경하면 됩니다. 그러면 해당하는 결과가 즉시 자동으로 표시됩니다.
2. 다중 VLOOKUP 수식으로 동적 데이터 유효성 검사하기
두 번째 방법은 데이터 유효성 검사 도구와 이중 VLOOKUP 함수를 사용하여 주어진 조건에 맞는지 데이터의 유효성을 검증하는 것입니다. 조건을 충족하면 TRUE, 충족하지 않으면 FALSE가 표시됩니다. 이번에는 가전제품 가격을 나타내는 새로운 데이터 세트를 사용합니다. 각 제품마다 가격의 하한선과 상한선을 설정해 두었으며, 이중 VLOOKUP 함수를 통해 입력된 가격이 조건에 부합하는지 확인해 보겠습니다.
단계별 방법:
- D11 셀에 아래 수식을 입력합니다.
=AND(C11>=VLOOKUP(B11,B5:D8,2,0),C11<=VLOOKUP(B11,B5:D8,3,0))
- Enter 키를 누르면 결과가 표시되며, 이 경우 TRUE가 반환됩니다.
- 마지막으로 채우기 핸들을 아래로 드래그하여 나머지 결과도 모두 구합니다.
모든 출력 결과는 아래와 같습니다.
수식 분석
➥ C11<=VLOOKUP(B11,B5:D8,3,0)
여기서 VLOOKUP 함수는 B11 셀의 값에 해당하는 가격 상한선을 찾습니다. 그런 다음 엑셀은 C11 셀의 값이 VLOOKUP 함수의 결과값보다 작거나 같은지 확인합니다. 따라서 결과는 다음과 같습니다.
TRUE
➥ C11>=VLOOKUP(B11,B5:D8,2,0)
이번에는 VLOOKUP 함수가 B11 셀의 값에 해당하는 가격 하한선을 찾습니다. 그리고 C11 셀의 값이 VLOOKUP 함수의 결과값보다 크거나 같은지 확인합니다. 결과는 다음과 같습니다.
TRUE
➥ AND(C11>=VLOOKUP(B11,B5:D8,2,0),C11<=VLOOKUP(B11,B5:D8,3,0))
마지막으로 AND 함수가 두 결과를 결합합니다. 두 결과가 모두 TRUE이면 최종 결과는 TRUE이고, 하나라도 FALSE이면 FALSE를 반환합니다. 따라서 최종 출력은 다음과 같습니다.
TRUE
결론
지금까지 소개한 두 가지 방법만 익히면 엑셀 데이터 유효성 검사와 사용자 지정 VLOOKUP 수식을 자유롭게 활용할 수 있습니다. 궁금한 점이 있다면 댓글로 언제든지 문의해 주시고, 많은 피드백 부탁드립니다.
함께 보면 좋은 관련 글
- 엑셀에서 여러 조건에 대한 사용자 지정 데이터 유효성 검사 적용하기 (4가지 예제)
- 엑셀 데이터 유효성 검사 수식에서 IF문 사용하는 방법 (6가지 방법)
- 엑셀에서 색상과 함께 데이터 유효성 검사 활용하기 (4가지 방법)
- 다른 시트의 데이터로 유효성 검사 목록 만들기 (6가지 방법)
- 엑셀 VBA로 배열에서 데이터 유효성 검사 목록 만들기