엑셀 VLOOKUP 함수로 여러 열의 데이터를 한 번에 추출하는 다중 값 검색 방법을 총정리했습니다. 중괄호({}) 배열 수식을 활용해 반복 입력 없이 실무 시간을 단축하는 꿀팁과 엑셀 버전별 주의사항을 지금 바로 확인해 보세요.
VLOOKUP 함수로 여러 열 데이터 동시 추출이 필요한 이유
기존 VLOOKUP 반복 입력의 비효율성
엑셀을 사용할 때 하나의 고유 코드를 기준으로 분류, 상품명, 단가 등 여러 개의 열 정보를 동시에 가져와야 하는 경우가 많습니다. 보통은 각각의 셀마다 VLOOKUP 함수를 복사하고 열 번호를 하나씩 수정하는 번거로운 방식을 사용하곤 합니다.
이러한 반복 작업은 대량의 데이터를 처리할 때 실수를 유발하기 쉽고 업무 효율을 크게 떨어뜨립니다. 하나의 수식만으로 원하는 모든 열의 데이터를 한 번에 찾아오면 수식 관리도 단순해지고 가독성도 높아집니다.
중괄호{} 배열을 활용한 VLOOKUP 다중 값 일괄 검색 방법
Step 1: 기준값 지정 및 VLOOKUP 기본 수식 입력
가장 먼저 값을 찾아올 첫 번째 빈 셀을 선택하고 VLOOKUP 함수를 시작합니다. 정보를 검색할 기준이 되는 고유 코드(예: 코드명 991)가 입력된 셀을 첫 번째 인수로 지정합니다.
검색할 값이 위치한 셀 주소를 입력한 뒤 쉼표(,)를 찍어 다음 단계로 넘어갑니다.
Step 2: 참조 표 전체 범위 선택 및 절대참조 지정
두 번째 인수는 데이터를 찾아올 원본 표의 범위를 지정하는 단계입니다. 코드명부터 시작해서 필요한 모든 데이터가 포함되도록 마우스로 드래그하여 전체 블록을 설정합니다.
위 사진처럼 원본 테이블의 데이터 영역 전체를 명확하게 지정해 주어야 오류 없이 값을 매칭할 수 있습니다.
Step 3: 중괄호{}를 이용한 다중 열 인덱스 번호 입력
이 방식의 가장 핵심이 되는 부분으로, 세 번째 인수에 단일 숫자가 아닌 중괄호{}를 사용해 가져올 열 번호들을 연속으로 입력합니다. 원본 표에서 내가 추출하고자 하는 열들이 몇 번째에 위치해 있는지 세어보고 그 순서대로 적어줍니다.
위 사진처럼 분류(3번째), 상품명(2번째), 단가(4번째) 열을 순서대로 가져오고 싶다면 {3,2,4}와 같이 중괄호 안에 쉼표로 구분하여 입력합니다.
Step 4: 배열 수식 적용 및 실시간 연동 결과 확인
마지막 인수로 정확한 일치를 뜻하는 0(또는 FALSE)을 입력하고 괄호를 닫아 수식을 마무리합니다. 아래 사진과 같이 전체 수식이 완성되면 엔터(Enter)를 누릅니다. 최신 엑셀 버전에서는 엔터만 누르면 옆으로 값이 알아서 펼쳐집니다.
수식이 정상적으로 실행되면 아래 이미지처럼 코드명에 매칭되는 분류, 상품명, 단가 데이터가 한 번에 완벽하게 채워집니다.
이렇게 완성된 수식은 동적 연동이 가능하므로 매우 실용적입니다. 아래 이미지처럼 기준이 되는 코드명만 다른 숫자로 변경하면 우측의 모든 정보가 실시간으로 자동 변경됩니다.
아래 사진도 마찬가지로 코드명 111과 같이 다른 행의 데이터도 수식 하나로 완벽하게 대응할 수 있어 장부 관리가 매우 쉬워집니다.
VLOOKUP 다중 값 추출 시 반드시 알아야 할 주의사항
정확한 열 순서 매칭과 중괄호 입력 오류 방지
중괄호{} 안에 열 번호를 입력할 때는 원본 데이터 표의 맨 왼쪽 열(기준열)을 1번으로 잡고 오른쪽으로 가면서 숫자를 정확하게 세어야 합니다. 또한 내가 값을 출력하고자 하는 행의 소제목 순서(분류, 상품명, 단가)와 중괄호 안의 열 순서가 완벽히 일치하는지 확인해야 엉뚱한 데이터가 들어오는 것을 막을 수 있습니다.
구버전 엑셀을 사용하는 환경이라면 수식을 입력한 뒤 그냥 엔터를 누르면 오류가 나거나 첫 번째 값만 나올 수 있습니다. 이럴 때는 값을 채울 범위를 마우스로 미리 블록 지정한 뒤 수식을 입력하고, 마지막에 키보드의 Ctrl + Shift + Enter를 동시에 눌러 배열 수식으로 박아주어야 정상 작동합니다.
자주 묻는 질문
Q1. 중괄호{} 안에 넣는 열 번호의 기준은 무엇인가요?
A1. VLOOKUP 수식에서 지정한 ‘참조 범위(두 번째 인수)’의 맨 첫 번째 열이 무조건 기준(1번)이 됩니다. 그 첫 열을 기준으로 오른쪽으로 한 칸씩 이동할 때마다 2번, 3번, 4번 순서로 숫자가 커지므로, 내가 가져오고 싶은 열이 원본 범위에서 몇 번째 칸에 있는지 세어서 중괄호에 넣으면 됩니다.
Q2. 수식을 입력했는데 옆 칸으로 값이 안 퍼지고 #N/A 오류나 첫 값만 나와요.
A2. 엑셀 최신 버전이 아닐 경우 배열 분산 기능이 지원되지 않아 첫 번째 값만 출력될 수 있습니다. 이 경우 수식을 작성하기 전에 값이 들어갈 가로 셀들을 미리 마우스로 드래그하여 블록을 잡은 상태에서 수식을 입력하고, 마무리를 Ctrl + Shift + Enter로 누르면 해결됩니다.
Q3. 원본 표의 데이터 위치가 바뀌면 수식 결과도 자동으로 업데이트되나요?
A3. 중괄호 안에 숫자를 직접 고정값으로 넣는 방식이기 때문에, 원본 데이터 표의 열 순서 자체가 통째로 바뀌거나 중간에 새로운 열이 삽입되면 엉뚱한 열의 값을 가져올 수 있습니다. 만약 원본 구조가 자주 바뀌는 문서라면 VLOOKUP 대신 INDEX와 MATCH 함수를 조합하여 구조적 연동을 만드는 것을 추천합니다.