엑셀에서 값을 찾아 원하는 셀에 넣는 모든 방법 비교: VLOOKUP, XLOOKUP, INDEX/MATCH, FILTER, OFFSET까지
엑셀에서 가장 자주 하는 작업 중 하나가 바로 기준값을 찾고, 원하는 셀에 결과를 넣는 작업입니다. 거래처 코드로 거래처명을 불러오거나, 상품명으로 가격을 채우거나, 여러 조건에 맞는 값을 가져오는 일이 대표적입니다. 그런데 막상 하려면 함수가 너무 많아서 어떤 방법을 써야 할지 헷갈립니다.
이 글에서는 엑셀 값을 찾아 넣는 모든 대표 방법을 한 번에 정리하고, 각 방법의 장점·단점·추천 상황까지 비교해드립니다. 초보자는 물론, 실무에서 더 깔끔한 방법을 찾는 분에게도 도움이 되도록 예제와 함께 설명하겠습니다.
빠른 결론: 어떤 방법을 쓰면 될까?
- 가장 무난하고 최신 방식: XLOOKUP
- 구버전 호환이 중요할 때: VLOOKUP 또는 INDEX/MATCH
- 왼쪽 조회·유연한 조회: INDEX/MATCH
- 여러 결과를 한 번에 뽑고 싶을 때: FILTER
- 동적으로 참조 위치를 바꿔야 할 때: OFFSET
- 단순 수동 입력으로 끝날 때: 찾기/바꾸기, 붙여넣기, Power Query
예제 데이터
| 상품코드 | 상품명 | 카테고리 | 단가 | 재고 |
|---|---|---|---|---|
| P1001 | 노트북 | 전자기기 | 950000 | 12 |
| P1002 | 마우스 | 전자기기 | 25000 | 55 |
| P1003 | 키보드 | 전자기기 | 49000 | 34 |
| P1004 | 모니터 | 전자기기 | 219000 | 8 |
| P1005 | 의자 | 가구 | 89000 | 20 |
예를 들어 A10 셀에 상품코드 P1003가 입력되어 있고, B10 셀에 해당 상품명을 불러오고 싶다고 가정하겠습니다.
1. VLOOKUP 함수로 값을 찾아 넣는 방법
가장 유명한 조회 함수입니다. 입력한 값을 기준으로 표의 첫 번째 열에서 찾고, 오른쪽 열의 값을 반환합니다.
=VLOOKUP(A10,$A$2:$D$6,2,FALSE)위 수식은 A10의 상품코드를 A2:D6 범위 첫 열에서 찾고, 두 번째 열인 상품명을 반환합니다.
장점
- 사용자가 가장 많아 검색 자료가 많음
- 초보자도 구조를 익히기 쉬움
- 구버전 Excel 호환성이 좋음
단점
- 오른쪽 방향 조회만 가능
- 열 번호를 숫자로 넣어야 해서 열 삽입 시 깨지기 쉬움
- 복잡한 조건 조회에 불편함
이럴 때 추천
회사에서 여전히 구버전 Excel을 사용하고 있고, 단순한 코드→결과 조회만 하면 될 때 적합합니다.
2. XLOOKUP 함수로 값을 찾아 넣는 방법
XLOOKUP은 VLOOKUP의 단점을 많이 보완한 최신 조회 함수입니다. Microsoft 지원 문서와 실무 비교 자료에서도 기존 VLOOKUP, HLOOKUP, INDEX/MATCH를 상당 부분 대체할 수 있는 함수로 소개됩니다.
=XLOOKUP(A10,$A$2:$A$6,$B$2:$B$6)장점
- 왼쪽·오른쪽 방향 모두 조회 가능
- 찾을 범위와 반환 범위를 따로 지정해 구조가 명확함
- 못 찾았을 때 표시할 문구를 쉽게 넣을 수 있음
단점
- 구버전 Excel에서는 사용할 수 없음
- 회사 PC 버전이 오래되면 파일 공유 시 문제가 생길 수 있음
실무용 추천 수식
=XLOOKUP(A10,$A$2:$A$6,$D$2:$D$6,"없음")상품코드에 맞는 단가를 찾되, 없으면 “없음”으로 표시합니다.
3. INDEX/MATCH 조합으로 값을 찾아 넣는 방법
INDEX는 위치의 값을 가져오고, MATCH는 값의 위치를 찾습니다. 두 함수를 합치면 매우 유연한 조회가 가능합니다. Microsoft 지원 문서도 VLOOKUP 외 대안으로 INDEX/MATCH를 함께 안내합니다.
=INDEX($B$2:$B$6,MATCH(A10,$A$2:$A$6,0))장점
- 왼쪽 조회 가능
- 열 삽입·삭제에 강함
- 다중 조건 확장에 유리함
단점
- 초보자에게 구조가 어렵게 느껴질 수 있음
- 수식이 길어질수록 관리가 불편함
이럴 때 추천
XLOOKUP을 못 쓰는 환경인데, VLOOKUP보다 더 안정적이고 유연한 방식이 필요할 때 추천합니다.
4. FILTER 함수로 여러 값을 한 번에 가져오는 방법
FILTER는 조건에 맞는 여러 행을 한 번에 반환합니다. VLOOKUP이나 XLOOKUP처럼 한 칸에 한 값만 넣는 방식보다, 조건 결과 전체를 한 번에 뿌릴 때 매우 강력합니다. 비교 글에서도 XLOOKUP, INDEX/MATCH와 함께 자주 언급됩니다.
=FILTER(A2:D6,C2:C6="전자기기")카테고리가 전자기기인 행만 한 번에 표시합니다.
장점
- 조건에 맞는 여러 결과를 즉시 표시
- 보고서·대시보드 제작에 유리
- 별도 보조열 없이도 깔끔한 결과 가능
단점
- 동적 배열을 지원하지 않는 구버전에서는 사용 불가
- 한 개 값만 넣는 전통적 조회와는 사용 방식이 다름
5. OFFSET 함수로 참조 위치를 이동해 값을 넣는 방법
OFFSET은 기준 셀에서 몇 행, 몇 열 떨어진 위치의 값을 참조합니다. 직접 “찾기” 함수는 아니지만, MATCH와 조합하면 동적으로 위치를 계산해 값을 가져올 수 있습니다.
=OFFSET(A2,MATCH(A10,A2:A6,0)-1,1)상품코드를 찾은 뒤, 그 행의 오른쪽 1칸 값을 가져오는 구조입니다.
장점
- 유동적인 참조 범위를 만들기 좋음
- 동적 차트, 유동 범위 설정에 강함
단점
- 가독성이 떨어질 수 있음
- 휘발성 함수라 데이터가 많으면 성능 부담 가능
- 조회 전용 함수로는 XLOOKUP, INDEX/MATCH보다 직관성이 떨어짐
6. HLOOKUP 함수는 언제 쓰나?
HLOOKUP은 가로 방향 표에서 값을 찾을 때 사용합니다. 하지만 실무 표 대부분이 세로 구조라 사용 빈도는 낮습니다. XLOOKUP이 있으면 대체 가능한 경우가 많습니다.
=HLOOKUP(B1,$A$1:$E$3,3,FALSE)7. 찾기 및 바꾸기, 수동 참조, Power Query 방법
찾기 및 바꾸기
일회성 수정에는 빠르지만 자동화가 안 됩니다.
직접 셀 참조
위치가 고정된 간단한 시트에서는 빠르지만, 데이터가 바뀌면 약합니다.
Power Query
두 표를 기준값으로 병합해 원하는 값을 붙이는 방식입니다. 반복 업무나 대량 데이터 정리에 매우 강력하지만, 초보자에게는 진입장벽이 있습니다.
방법별 장단점 비교표
| 방법 | 장점 | 단점 | 추천 상황 |
|---|---|---|---|
| VLOOKUP | 쉽고 익숙함, 구버전 호환 | 오른쪽 조회만 가능, 열번호 취약 | 단순 조회, 구버전 환경 |
| XLOOKUP | 직관적, 왼쪽 조회 가능, 오류 처리 편리 | 구버전 미지원 | 최신 Excel, 대부분의 실무 |
| INDEX/MATCH | 유연함, 안정적, 왼쪽 조회 가능 | 초보자에게 어려움 | 구버전 + 유연성 필요 |
| FILTER | 여러 결과를 한 번에 반환 | 동적 배열 미지원 버전 사용 불가 | 조건별 목록 출력 |
| OFFSET | 동적 참조 가능 | 가독성·성능 부담 | 특수한 유동 참조 |
| Power Query | 대량 데이터 병합에 강함 | 초기 학습 필요 | 반복 정리 작업 |
실무에서 어떤 방법을 선택해야 할까?
- 최신 Excel을 쓴다: XLOOKUP부터 고려
- 여러 결과를 목록으로 뽑는다: FILTER
- 구버전 호환이 중요하다: INDEX/MATCH
- 아주 단순하고 공유 대상도 익숙하다: VLOOKUP
- 반복 병합 작업이 많다: Power Query
자주 발생하는 오류와 해결법
| 증상 | 원인 | 해결법 |
|---|---|---|
| #N/A | 찾는 값이 없음, 공백 차이 | TRIM, CLEAN 사용 또는 정확히 일치 확인 |
| #VALUE! | 범위 크기 불일치 | lookup_array와 return_array 크기 확인 |
| 엉뚱한 값 반환 | 근사일치 설정 | VLOOKUP 4번째 인수 FALSE, MATCH 0 사용 |
| 수식 복사 시 범위 밀림 | 상대참조 | $로 절대참조 고정 |
관련 글
마무리
엑셀에서 값을 찾아 원하는 셀에 넣는 방법은 하나가 아닙니다. 가장 중요한 것은 “함수를 많이 아는 것”보다 상황에 맞는 방법을 고르는 것입니다. 최신 버전이라면 XLOOKUP이 가장 무난하고, 여러 결과가 필요하면 FILTER, 구버전 호환과 유연성이 중요하면 INDEX/MATCH가 여전히 강력합니다. 실무에서는 한 가지 방법만 고집하지 말고, 데이터 구조와 공유 환경에 따라 가장 적절한 방법을 선택해보세요.
FAQ
VLOOKUP과 XLOOKUP 중 무엇이 더 좋나요?
최신 Excel을 사용한다면 대부분의 경우 XLOOKUP이 더 편리합니다.
왼쪽에 있는 값을 조회하려면 어떻게 하나요?
XLOOKUP 또는 INDEX/MATCH를 사용하면 됩니다.
조건에 맞는 여러 행을 한 번에 가져오려면?
FILTER 함수가 가장 적합합니다.