Categories: 엑셀

엑셀 드롭 다운 리스트 목록 만들기 Vlookup 활용법

엑셀에서 셀 칸에 세모 버튼을 눌러 목록을 활성화 하는 기능이 드롭다운 기능이라고 합니다. 직접 타이핑 하지 않고 드롭 다운 목록에서 선택하기 때문에 오타 방지와 데이터 관리가 쉬워진다는 장점이 있습니다. 이러한 엑셀에서 드롭다운 선택시 값 가져오기 기능은 어떻게 하는지 알아보겠습니다.

드롭다운 선택시 값 가져오기

우선 위의 예제와 같은 드롭 다운 기능과 Vlookup 함수를 활용해 간단한 데이터를 관리하는 방법을 예제로 나타내 봐습니다. 예제와 같은 엑셀 드롭 다운 기능을 활용하기 위해 아래 내용을 순차적으로 따라해 주시면 쉽게할 수 있습니다.

드롭 다운 목록 만들기

드롭 다운 목록을 표시하기 위해 다른 시트나 셀에서 데이터를 불러올 수 있는 리스트가 필요합니다. 임의로 sheet2를 만들어 아래와 같이 입력해 주었습니다.

데이터를 관리하고 있다면 대부분 이러한 형태로 관리하고 있을 거라고 예상합니다. 이렇게 정리되어 다른 시트나 파일에서 드롭 영역을 지정해 드롭 다운 메뉴를 만들어 사용할 수 있습니다. 예제와 같이 드롭다운을 구성하기 위해 Sheet2에 데이터를 입력해 주세요.

데이터 유효성 검사 설정

드롭 다운 목록을 활용할 sheet1의 적용할 셀을 선택하고, 데이터 메뉴-데이터 유효성 검사 기능을 활성화 해줍니다.

데이터 유효성 검사를 설정을 활성화 시키게되면, 제한 대상에 ‘모든 값’을 ‘목록’으로 설정을 바꿔주고, 드롭다운 표시가 체크가 되어 있는지 확인합니다.

그리고 sheet 2에 준비되어 있는 데이터 범위에 코드 부분을 ‘원본’ 영역에 적용 시켜주면 sheet 1에 선택한 부분에 ‘A1234,B5457,C7789’코드가 드롭 다운 되어 있는 것을 확인할 수 있습니다.

Vlookup 함수 추가

Vlookup 함수를 같이 활용하면 드롭 다운 목록에서 선택한 데이터 값이 일치했을 경우 해당 데이터와 연관된 데이터를 가져올 수 있게됩니다.

Vlookup 함수를 설정하는 방법은 아래와 같이 설정할 수 있습니다.

위 Vlookup 함수 4가지 입력 조건을 쉽게 설명해 드리겠습니다.

Lookup_value = 드롭 다운 목록에서 선택한 위치 지정
Table_array = Sheet2의 데이터 범위
Col_index_num = Sheet2의 데이터 범위 내 ‘품명’이라는 데이터 위치( 코드 1, 품명 2, 재고 3, 단가 4)
Range_lookup = 0과 1로 구분되어 FALSE 또는 TRUE로 표현됩니다. (쉽게 말해 FALSE는 근사 값을 표시할 수 있고, TRUE는 정확한 값만 표시하게 됩니다. )

Table_array 범위를 지정할 때 Sheet2!$F$7:$I$9 절대 값을 사용하게 되면, 수직으로 복사할 때 유용할 수 있습니다.

그리고 ‘#N/A’가 표시되는 이유는 코드 열에 값이 지정하지 않을 경우 표기 됩니다. 하지만 IFERROR 함수를 활용하게되면 쉽게 해결할 수 있습니다.

수식은 아래와 같습니다.

#N/A 안보이게 하는 IFERROR 함수 추가
=IFERROR(VLOOKUP(C6,Sheet2!$F$7:$I$9,2,0),””)

IFERROR 사용해 에러나 오류가 있으면, 공백으로 표시하겠다는 수식이니 참고해 사용하시면 좋을 듯합니다.

마치며

글로 엑셀 기능을 설명 하자니 설득력 있게 설명이 잘 정리되어 있는지는 모르겠지만, 엑셀의 모든 기능은 엄청난 스킬 처럼 보일 수 있지만, 막상 기능을 숙지하다보면 누구나 쉽게 기능을 사용하고 더 나아가 응용까지 할 수 있게됩니다.기본적으로 드롭다운 기능과 Vlookup 함수만 조금만 이해하게되면 업무 효율성을 좀더 높일 수 있게 됩니다.

아래 링크는 제가 직장인들이 자주 사용하는 엑셀 기능 정리한 내용입니다.

cdling