실무에서 VLOOKUP 함수는 직장인이 가장 많이 사용하는 엑셀의 대표적인 함수입니다. 하지만 VLOOKUP 함수를 기본 형태로만 사용하면 왼쪽 방향 검색이나 단어 포함 검색 같은 상황에서 제한이 발생합니다.
① 열 위치 옮기기 (왼쪽 방향 검색)
② 열 번호 쉽게 확인하기 (넓은 데이터 검색)
③ 단어 포함 검색 방법
④ 가로/세로 양방향 검색 공식
실무에 바로 적용하는 VLOOKUP 4가지 꿀팁을 알아봅니다.
이 글에서는 실무에서 자주 마주치는 VLOOKUP의 한계를 해결하는 실전 꿀팁 4가지를 정리했습니다. VLOOKUP 함수의 기초 사용법은 아래 게시글 링크를 참고하세요!
- 열 위치 옮기기 : VLOOKUP 함수는 범위에서 첫번째 열에 있는 값을 기준으로 오른쪽에 있는 데이터만 검색할 수 있습니다. 따라서 아래 그림과 같이 제품코드(C열)을 기준으로 제조사(B열)를 검색할 경우, VLOOKUP 규칙에 따라 제조사(B열)을 제품코드(C열)의 오른쪽으로 먼저 옮겨줘야 합니다.
VLOOKUP 함수는 찾을값의 오른쪽 방향으로만 검색 가능합니다.
- 이럴 때 Shift 키를 사용하면 편리합니다. 옮기려는 제조사(B열)을 선택한 후 Shift 키를 누른 상태로 범위의 가장자리를 드래그하면, 아래 그림처럼 초록색 선이 표시되어 옮길 지점을 확인할 수 있습니다.
SHIFT키를 누른 상태로 범위 가장자리를 드래그하면 밀어서 옮길 수 있습니다.
- 원하는 위치에서 마우스를 놓으면, 다른 열이 옆으로 밀리면서 제조사(B열)이 해당 위치로 깔끔하게 이동합니다. 이 외에도 Shift 단축키는 실무에서 다양하게 사용됩니다. Shift 단축키의 더 많은 활용 예제는 아래 1분 강의를 확인하세요!
- 열 번호 쉽게 확인하기 : VLOOKUP 함수로 넓은 범위를 검색할 때는 열 번호를 일일이 찾기 어렵습니다. 예를 들어, 아래 그림과 같은 표에서 특정 제품의 담당자를 검색하려면 담당자의 열 번호를 하나씩 세야 하는데요.
범위가 넓을 경우, 열 번호를 확인하기 어려울 수 있습니다.
- 이럴 때 이름 상자를 사용하면 편리합니다. 범위의 머리글을 드래그하면 아래 그림과 같이 이름 상자 또는 커서 오른쪽 아래에 표시되는 행x열 정보(1R x 1C...)로 선택한 범위의 열 개수를 쉽게 확인할 수 있습니다.
범위를 드래그할 때 이름상자에 표시되는 행x열 정보로 열 개수를 쉽게 확인할 수 있습니다.
- 단어 포함 검색하기 : VLOOKUP 함수 찾을 값에는 와일드카드(*, ?)를 사용할 수 있습니다. 예를 들어, "*사과*"를 입력하면 범위에서 사과를 포함하는 모든 값을 대상으로 검색을 실행합니다. 만약 다른 셀을 참조할 경우, =VLOOKUP("*"&A1&"*",...) 형태로 함수를 작성합니다.
=VLOOKUP("*사과*",B1:D10,2,0)
/ / 사과를 포함하는 값을 검색합니다.
=VLOOKUP("*"&A1&"*",B1:D10,2,0)
/ / A1셀에 작성된 단어를 포함하는 값을 검색합니다.
특정 단어를 포함하는 값을 검색합니다.
- 와일드카드(*, ?)의 자세한 설명은 아래 기초 입문 강의에서 알기 쉽게 정리했으니 참고하세요!😊
- 가로세로 양방향 검색 : VLOOKUP과 MATCH 함수를 함께 사용하면 가로와 세로 방향을 동시에 검색하는 VLOOKUP 공식을 만들 수 있습니다. MATCH 함수는 찾을 값이 범위에서 몇번 째에 위치하는지 순번을 반환합니다. 예를 들어, 아래 그림과 같이 머리글 범위에서 "단가"의 순번을 찾도록 MATCH 함수를 작성하면 결과값으로 3이 반환됩니다.
MATCH 함수는 범위에서 값이 위치하는 순번을 반환합니다.
- 따라서, VLOOKUP 함수의 열 번호로 MATCH 함수를 사용해서 가로와 세로 방향을 동시에 검색하는 양방향 VLOOKUP 공식을 완성할 수 있습니다.
=VLOOKUP(찾을값, 범위, MATCH(머리글, 머리글범위, 0), 0)
/ / 찾을값과 머리글 기준으로 가로/세로 양방향 검색을 수행합니다.
가로와 세로 방향을 동시에 검색하는 VLOOKUP 보고서가 완성됩니다.
