Ch09_함수를 사용하는 데이터 검색(1)

학습목표

이 장에서는 Excel의 찾기·참조 함수를 이용하여 필요한 데이터를 검색하고, 여러 함수의 차이와 활용 방법을 익힌다. 또한 Excel 365의 동적 배열 함수를 활용하여 조건에 맞는 데이터를 자동으로 추출·정렬하고, 여러 함수를 결합하여 실무형 데이터 검색 및 분석 기능을 구현한다.

---

Excel에서 데이터가 많아질수록 눈으로 값을 찾는 방식에는 한계가 있다. 실무에서는 상품코드, 사원번호, 학번과 같은 고유한 기준값을 이용하여 원하는 정보를 검색하고, 조건에 맞는 여러 데이터를 새로운 목록으로 추출하는 작업이 자주 발생한다.

이 장에서는 하나의 온라인 쇼핑몰 상품·판매 데이터를 사용하여 VLOOKUP, HLOOKUP, XLOOKUP, MATCH, INDEX를 익힌다. 이어서 RANDBETWEEN, RANDARRAY, FILTER, SORTBY, UNIQUE, SEQUENCE를 활용하여 데이터가 자동으로 확장되는 동적 검색 시스템을 만들어 본다.

---

Section 01. 찾기·참조 함수

데이터가 많아지면 원하는 값을 직접 눈으로 찾아내는 것은 비효율적이다. Excel에서는 찾기·참조 함수를 이용하여 상품코드, 사원번호, 학번 등 특정 기준값을 바탕으로 관련 정보를 자동으로 검색할 수 있다. 
이 절에서는 하나의 상품 판매 데이터를 계속 사용하면서 다음 함수를 학습한다.

함수	핵심기능	학습수준
VLOOKUP	세로 방향 데이터 검색	기본~중급
HLOOKUP	가로 방향 데이터 검색	기본~중급
XLOOKUP	검색범위와 반환범위를 독립적으로 지정	중요
MATCH	값의 상대적 위치 검색	중급
INDEX	지정된 위치의 값 반환	중급
INDEX+MATCH	위치 검색과 값 반환 결합	Expert

---

1. VLOOKUP 함수 — 세로 방향으로 데이터 검색하기

VLOOKUP 함수란?

VLOOKUP은 표의 첫 번째 열에서 원하는 값을 찾은 뒤, 같은 행의 다른 열에 있는 값을 반환하는 함수이다. 상품코드처럼 세로 방향으로 나열된 데이터를 기준으로 상품명, 카테고리, 단가 등을 검색할 때 사용할 수 있다.

=VLOOKUP(찾을값, 검색범위, 열번호, 일치방법)

✨ 실습 1. 상품명 찾기

B10 셀을 선택하고 다음 수식을 입력한다.

=VLOOKUP(A10,'원본데이터'!$L$5:$O$24,2,FALSE)

수식 분석

• A10 : 찾을 상품코드 P009
• $L$5:$O$24 : 검색할 상품마스터
• 2 : 검색범위에서 두 번째 열인 상품명 반환
• FALSE : P009와 정확하게 일치하는 값 검색

즉,

P009를 L열에서 찾고 → 같은 행의 두 번째 열 M열에 있는 상품명을 가져온다.

✨ 실습 2. 카테고리 찾기

C10 셀에는 다음 수식을 입력한다.

=VLOOKUP(A10,'원본데이터'!$L$5:$O$24,3,FALSE)

이번에는 열 번호가 3이다.

열	번호
L 상품코드	1
M 상품명	2
N 카테고리	3
O 단가	4

 

실습 3. 단가 찾기

D10 셀에는 다음 수식을 입력한다.

=VLOOKUP(A10,'원본데이터'!$L$5:$O$24,4,FALSE)

단가는 검색범위에서 네 번째 열이므로 4를 사용한다.

VLOOKUP 핵심

VLOOKUP은 반드시 찾을 값이 검색범위의 첫 번째 열에 있어야 한다.

---

2. HLOOKUP 함수 — 가로 방향으로 데이터 검색하기

HLOOKUP 함수란?

HLOOKUP은 VLOOKUP과 반대로 첫 번째 행에서 값을 검색하고, 아래쪽 행에서 결과를 반환하는 함수이다.

H는 Horizontal, 즉 가로 방향을 의미한다.

=HLOOKUP(찾을값, 검색범위, 행번호, 일치방법)

1분기	2분기	3분기	4분기
목표매출	목표매출	목표매출	목표매출
인센티브율	인센티브율	인센티브율	인센티브율

✨실습4.  3분기 목표매출 검색

B16 셀에 다음 수식을 입력한다.

=HLOOKUP(A16,'원본데이터'!$R$4:$U$6,2,FALSE)

수식의 의미는 다음과 같다.

• A16 : 3분기
• $R$4:$U$6 : 검색범위
• 2 : 범위의 두 번째 행에서 결과 반환
• FALSE : 정확하게 일치하는 3분기 검색

✨실습 5. 인센티브율 검색

C16 셀에 다음 수식을 입력한다.

=HLOOKUP(A16,'원본데이터'!$R$4:$U$6,3,FALSE)

인센티브율은 검색범위의 세 번째 행이므로 3을 사용한다

---

3. XLOOKUP 함수 — Excel 365의 핵심 검색 함수

XLOOKUP 함수란?

XLOOKUP은 Excel 365에서 중요하게 사용하는 검색 함수이다. VLOOKUP처럼 열 번호를 직접 계산할 필요 없이 검색할 범위와 결과를 반환할 범위를 각각 지정한다.

=XLOOKUP(찾을값, 검색범위, 반환범위, [찾지못한경우])

✨실습 6. XLOOKUP으로 상품명 검색

• A21:E22 영역을 확인한다.

B22 셀의 수식이다.

=XLOOKUP(A22,'원본데이터'!$L$5:$L$24,'원본데이터'!$M$5:$M$24,"상품 없음")

VLOOKUP과 비교하여 보자.

VLOOKUP 방식

=VLOOKUP(A22,'원본데이터'!$L$5:$O$24,2,FALSE)

XLOOKUP 방식

=XLOOKUP(A22,'원본데이터'!$L$5:$L$24,'원본데이터'!$M$5:$M$24,"상품 없음")

XLOOKUP에서는 열 번호 2가 없다.

대신,

L5:L24 → 어디에서 찾을 것인가?

M5:M24 → 어디에서 결과를 가져올 것인가?

를 직접 지정한다.

---

실습 7. 카테고리 찾기

C22 셀에는 다음 수식이 있다.

=XLOOKUP(A22,'원본데이터'!$L$5:$L$24,'원본데이터'!$N$5:$N$24,"상품 없음")

검색 범위는 그대로 L열이지만 반환 범위가 N열로 변경되었다.

실습 8. 단가 찾기

D22 셀에는 다음과 같이 입력한다.

=XLOOKUP(A22,'원본데이터'!$L$5:$L$24,'원본데이터'!$O$5:$O$24,"상품 없음")

실습 9. 검색 결과가 없을 때 메시지 출력하기

E22 셀에는 일부러 존재하지 않는 P999를 검색하도록 되어 있다.

=XLOOKUP("P999",'원본데이터'!$L$5:$L$24,'원본데이터'!$M$5:$M$24,"상품 없음")

P999가 존재하지 않기 때문에 결과는 다음과 같다.

상품 없음

이것은 XLOOKUP의 중요한 장점이다.

• VLOOKUP에서는 검색값이 존재하지 않으면 일반적으로 다음 오류가 발생한다.
• XLOOKUP에서는 네 번째 인수에 원하는 메시지를 지정할 수 있다.

---

4. MATCH 함수 — 값이 몇 번째에 있는지 찾기

MATCH 함수란?

MATCH는 상품명이나 단가를 직접 가져오는 함수가 아니다.  검색값이 지정한 범위에서 몇 번째 위치에 있는지 알려주는 함수이다.

=MATCH(찾을값, 검색범위, 일치유형)

 

실습 10. P012의 위치 찾기

Section01_찾기참조 시트의 A27:E28 영역을 확인한다.

A28에는 다음 값이 입력되어 있다.

P012

B28의 수식은 다음과 같다.

=MATCH(A28,'원본데이터'!$L$5:$L$24,0)

여기에서 마지막 0은 정확히 일치하는 값을 찾으라는 의미이다.

결과가 예를 들어 12라면,

P012가 L5:L24 범위에서 12번째 위치에 있다는 뜻이다.

주의할 점은 실제 Excel 행 번호가 아니라 지정한 범위 안에서의 상대적인 위치라는 것이다.

---

5. INDEX 함수 — 지정한 위치의 값을 가져오기

INDEX 함수란?

INDEX는 지정된 범위에서 몇 번째 위치의 값을 가져올 것인지 지정하는 함수이다.

=INDEX(범위, 행번호)

MATCH와 역할을 비교하면 쉽게 이해할 수 있다.

MATCH : 위치를 찾는다.
INDEX : 그 위치의 값을 가져온다.

 

실습 11. MATCH에서 찾은 위치로 상품명 가져오기

B28은 MATCH 결과이다.

=MATCH(A28,'원본데이터'!$L$5:$L$24,0)

C28에는 다음 수식이 들어 있다.

=INDEX('원본데이터'!$M$5:$M$24,B28)

예를 들어 MATCH 결과가 12라면 Excel은 다음과 같이 해석한다.

=INDEX('원본데이터'!$M$5:$M$24,12)

즉, 상품명 범위에서 12번째 값을 가져온다.

---

6. INDEX + MATCH — 두 함수를 하나로 결합하기

위 실습에서는 MATCH 결과를 B28에 먼저 계산하고, 이를 INDEX에서 사용하였다. 하지만 실제 업무에서는 두 함수를 하나로 결합한다.

실습 12. 상품명 검색하기

D28 셀의 수식이다.

=INDEX('원본데이터'!$M$5:$M$24,MATCH(A28,'원본데이터'!$L$5:$L$24,0))

계산 순서

Excel은 안쪽 함수부터 계산한다.

① MATCH 실행

MATCH(A28,'원본데이터'!$L$5:$L$24,0)

↓ P012 위치 찾기

② INDEX 실행

INDEX('원본데이터'!$M$5:$M$24,찾은위치)

↓ 해당 위치의 상품명 반환

---

실습 13. INDEX + MATCH로 단가 검색하기

E28 셀에서는 같은 원리를 사용하여 단가를 검색한다.

=INDEX('원본데이터'!$O$5:$O$24,MATCH(A28,'원본데이터'!$L$5:$L$24,0))

---

8. Expert 연습 —  수식 작성하기

D34 셀: P005 상품명 찾기

=VLOOKUP(B34,'원본데이터'!$L$5:$O$24,2,FALSE)

D35 셀: 4분기 인센티브율 찾기

=HLOOKUP(B35,'원본데이터'!$R$4:$U$6,3,FALSE)

D36 셀: 검색 결과가 없으면 "상품 없음"을 출력하도록 한다.

=XLOOKUP(B36,'원본데이터'!$L$5:$L$24,'원본데이터'!$O$5:$O$24,"상품 없음")

D37 셀: P020이 상품코드 목록에서 몇 번째에 있는지 구한다.

=MATCH(B37,'원본데이터'!$L$5:$L$24,0)

D38 셀: P003 카데고리 반환

=INDEX('원본데이터'!$N$5:$N$24,MATCH(B38,'원본데이터'!$L$5:$L$24,0))

---