엑셀 VLOOKUP · HLOOKUP 함수 — 정확히 일치 · 구간 찾기 · $ (컴활 2급 실기 유형 연습 3문제)
다른 표에서 값을 찾아오는 함수입니다. 세로로 놓인 표는 VLOOKUP, 가로로 놓인 표는 HLOOKUP. 중급 1 · 2강에 이어 시험 문제 꼴로 새로 만든 연습 문제 3개(기본 · 응용 · 함정)를 이 PC 엑셀에서 직접 풀고, 넷째 인수(FALSE · TRUE) · 열 번호 · $ 를 틀렸을 때 칸마다 어떤 값이 나오는지 적었습니다.
이렇게 쟀습니다
문제 문장 · 이름 · 코드 · 숫자는 모두 이 강을 위해 새로 만들었습니다(문제 번호는 중급 1 · 2강에서 이어 7~9). 실습 파일을 이 PC 엑셀(Microsoft 365)에서 열어 맨 위 답 칸에 수식을 넣고 아래로 채운 뒤(Ctrl+D 와 같은 '아래로 채우기') 칸에 보이는 값을 셀 주소별로 읽었습니다. 자주 틀리는 식 9개도 같은 칸에 넣어 결과를 읽었습니다. 그림은 범위를 PDF 로 내보내 만든 것입니다.
대한상공회의소 문제가 아닌 연습용 자작 문제입니다. 함수 인수 창의 칸 이름 표기와 컴활 실기의 채점 기준 원문은 이번에 확인하지 못했습니다(확인 안 됨).
VLOOKUP 꼴 — 찾을 값 · 표 · 열 번호 · FALSE
문제 7 (기본) [표7]에서 제품코드[A3:A10]를 이용하여 제품표[E3:G7]에서 단가를 찾아 단가[C3:C10]에 표시하시오. ▶ VLOOKUP 함수 사용
| VLOOKUP 의 칸 | 문제 7 에 넣는 값 | 뜻 |
|---|---|---|
| 첫째(찾을 값) | A3 | 이 코드를 표의 첫 열에서 찾음 |
| 둘째(표 범위) | $E$3:$G$7 | 제품표 — 머리글 행은 빼고, 아래로 채워도 움직이지 않게 $ |
| 셋째(열 번호) | 3 | 표 범위 안에서 셋째 열 = 단가(E 가 1, F 가 2, G 가 3) |
| 넷째(찾는 방법) | FALSE | 정확히 일치하는 코드만 — 없으면 #N/A |
| A | B | C | E | F | G | |
|---|---|---|---|---|---|---|
| 1 | [표7] 문구 주문 내역 | |||||
| 2 | 제품코드 | 수량 | 단가 | 제품코드 | 제품명 | 단가 |
| 3 | P-103 | 12 | P-101 | 볼펜 | 500 | |
| 4 | P-101 | 30 | P-102 | 노트 | 1,200 | |
| 5 | P-105 | 4 | P-103 | 형광펜 | 800 | |
| 6 | P-102 | 10 | P-104 | 지우개 | 300 | |
| 7 | P-104 | 25 | P-105 | 파일철 | 1,500 | |
| 8 | P-101 | 18 | ||||
| 9 | P-103 | 6 | ||||
| 10 | P-105 | 9 | ||||
![엑셀 표 [표7] 문구 주문 내역. A 열 제품코드, B 열 수량, C 열 단가에 800, 500, 1,500, 1,200, 300, 500, 800, 1,500 이 들어 있고 빨간 테두리가 있다. 오른쪽 E2:G7 에 제품코드 · 제품명 · 단가 제품표가 있다.](/lectures/excel/mid-03-q7-result.png)
| C3 에 넣고 아래로 채운 식 | 결과(이 PC 엑셀) |
|---|---|
=VLOOKUP(A3,$E$3:$G$7,3,FALSE) 정답 | 800 · 500 · 1,500 · 1,200 · 300 · 500 · 800 · 1,500 |
=VLOOKUP(A3,$E$3:$G$7,2,FALSE) 열 번호 2 | 형광펜 · 볼펜 · 파일철 … — 제품명이 나옴(8칸 틀림) |
=VLOOKUP(A3,$E$3:$G$7,7,FALSE) 시트 열 번호(G=7) | 모두 #REF! — 표 범위가 3열뿐 |
=VLOOKUP(A3,E3:G7,3,FALSE) 표 범위에 $ 없음 | 800 · #N/A · 1,500 · #N/A · #N/A · #N/A · #N/A · #N/A(6칸) |
![엑셀 표 [표7] 에 $ 없이 채운 VLOOKUP 결과. C 열이 800, #N/A, 1,500, #N/A, #N/A, #N/A, #N/A, #N/A 다. C3:C10 에 빨간 테두리가 있다.](/lectures/excel/mid-03-trap-relative.png)
$ 가 없으면 C10 의 식이 =VLOOKUP(A10,E10:G14,3,FALSE) — 제품표가 일곱 줄 내려가 빈 칸에서 찾습니다. C5(P-105)가 우연히 맞은 것은 E5:G9 안에 아직 P-105(7행)가 남아 있어서입니다. 다른 표를 가리키는 범위는 늘 $ 로 고정하세요.
문제 8 — HLOOKUP 과 구간 찾기(TRUE)
문제 8 (응용) [표8]에서 구매 금액[B14:B21]에 따른 할인율을 할인율표[F13:I14]를 참조하여 할인율[C14:C21]에 표시하시오. 할인율표의 구매 금액은 그 금액 '이상'부터 적용된다. ▶ HLOOKUP 함수 사용
표가 가로(13행에 금액, 14행에 할인율)로 놓여 있으면 HLOOKUP 을 씁니다. 칸은 VLOOKUP 과 같고 셋째 칸이 '열 번호' 대신 행 번호입니다. 그리고 '100,000 이상 5%'처럼 구간으로 찾을 때는 넷째 칸을 TRUE 로 둡니다 — 찾을 값 이하에서 가장 큰 금액을 찾아 그 아래 할인율을 돌려줍니다. 그러려면 첫 행(금액)이 작은 것부터 커지는 순서여야 합니다.
| A | B | C | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|
| 12 | [표8] 고객 구매 금액 | |||||||
| 13 | 고객 | 구매 금액 | 할인율 | 구매 금액 | 0 | 100,000 | 300,000 | 500,000 |
| 14 | 계바다 | 85,000 | 할인율 | 0% | 5% | 10% | 15% | |
| 15 | 곽이음 | 100,000 | ||||||
| 16 | 구나래 | 250,000 | ||||||
| 17 | 기솔찬 | 300,000 | ||||||
| 18 | 남은솜 | 499,000 | ||||||
| 19 | 노해밀 | 500,000 | ||||||
| 20 | 도아름 | 720,000 | ||||||
| 21 | 라온결 | 45,000 | ||||||
| 구매 금액(행) | 할인율(이 PC 엑셀) | 찾은 금액 |
|---|---|---|
| 85,000 (14) | 0% | 0 |
| 100,000 (15) | 5% | 100,000 — 딱 맞음 |
| 250,000 (16) | 5% | 100,000 |
| 300,000 (17) | 10% | 300,000 — 딱 맞음 |
| 499,000 (18) | 10% | 300,000 |
| 500,000 (19) | 15% | 500,000 — 딱 맞음 |
| 720,000 (20) | 15% | 500,000 |
| 45,000 (21) | 0% | 0 |
![엑셀 표 [표8] 고객 구매 금액. C14:C21 할인율이 0%, 5%, 5%, 10%, 10%, 15%, 15%, 0% 이고 빨간 테두리가 있다. 오른쪽 E13:I14 에 가로 할인율표가 있다.](/lectures/excel/mid-03-q8-result.png)
| C14 에 넣고 아래로 채운 식 | 결과(이 PC 엑셀) |
|---|---|
=HLOOKUP(B14,$F$13:$I$14,2,TRUE) 정답 | 0% · 5% · 5% · 10% · 10% · 15% · 15% · 0% |
=HLOOKUP(B14,$F$13:$I$14,2) 넷째 인수 생략 | 정답과 같음 — 생략하면 TRUE |
=HLOOKUP(B14,$F$13:$I$14,2,FALSE) 구간에 FALSE | #N/A · 5% · #N/A · 10% · #N/A · 15% · #N/A · #N/A(5칸) |
=VLOOKUP(B14,$F$13:$I$14,2,TRUE) 가로 표에 VLOOKUP | 모두 5%(6칸 틀림) |
![엑셀 표 [표8] 에 FALSE 로 쓴 HLOOKUP 결과. C 열이 #N/A, 5%, #N/A, 10%, #N/A, 15%, #N/A, #N/A 다. C14:C21 에 빨간 테두리가 있다.](/lectures/excel/mid-03-trap-false.png)
문제 9 — 정렬 안 된 표에서 넷째 인수를 빼면
문제 9 (함정) [표9]에서 부서코드[A25:A32]를 이용하여 부서표[E25:F30]에서 부서명을 찾아 부서명[C25:C32]에 표시하시오. ▶ VLOOKUP 함수 사용 ▶ 부서표는 부서코드 순으로 정렬되어 있지 않음
C25 에 =VLOOKUP(A25,$E$25:$F$30,2,FALSE) 를 넣고 채우면 영업팀 · 개발팀 · 총무팀 · 기획팀 · 회계팀 · 인사팀 · 영업팀 · 개발팀이 나왔습니다. 문제는 넷째 인수를 빼먹었을 때입니다. 생략은 TRUE(근삿값)와 같고, 근삿값 찾기는 첫 열이 정렬돼 있다고 보고 찾기 때문에 이 부서표(D4 · D1 · D6 · D2 · D5 · D3 순)에서는 결과가 엉망이 됩니다.
| 부서코드(행) | FALSE(정답) | 넷째 인수 생략(이 PC 엑셀) |
|---|---|---|
| D2 (25) | 영업팀 | #N/A |
| D6 (26) | 개발팀 | 개발팀 — 우연히 맞음 |
| D1 (27) | 총무팀 | #N/A |
| D3 (28) | 기획팀 | #N/A |
| D5 (29) | 회계팀 | 총무팀 — 오류 없이 틀림 |
| D4 (30) | 인사팀 | 인사팀 — 우연히 맞음 |
| D2 (31) | 영업팀 | #N/A |
| D6 (32) | 개발팀 | 개발팀 — 우연히 맞음 |
![엑셀 표 [표9] 에 넷째 인수를 뺀 VLOOKUP 결과. C 열이 #N/A, 개발팀, #N/A, #N/A, 총무팀, 인사팀, #N/A, 개발팀 이다. 오른쪽 부서표는 D4, D1, D6, D2, D5, D3 순으로 정렬되어 있지 않다.](/lectures/excel/mid-03-trap-omit.png)
#N/A 는 눈에 띄지만 D5 → 총무팀처럼 오류 없이 틀린 칸은 놓치기 쉽습니다. 코드 · 이름처럼 '딱 그 값'을 찾을 때는 FALSE, 점수 · 금액 구간을 찾을 때만 TRUE(그때는 표를 오름차순으로). 표 범위에 $ 없이 채운 식은 4칸(27 · 30 · 31 · 32행)이 #N/A 였습니다.
![엑셀 표 [표9] 를 수식 보기로 본 모습. C25 부터 C32 까지 =VLOOKUP(A25,$E$25:$F$30,2,FALSE) 가 행 번호만 바뀌며 들어 있고 빨간 테두리가 있다.](/lectures/excel/mid-03-q9-formula.png)
풀이 순서 한눈에
| 문제 | 누르는 칸 | 넣는 수식(그대로) | 그다음 |
|---|---|---|---|
| 7 | C3 | =VLOOKUP(A3,$E$3:$G$7,3,FALSE) | Enter → C3:C10 고르고 Ctrl+D |
| 8 | C14 | =HLOOKUP(B14,$F$13:$I$14,2,TRUE) | Enter → C14:C21 고르고 Ctrl+D |
| 9 | C25 | =VLOOKUP(A25,$E$25:$F$30,2,FALSE) | Enter → C25:C32 고르고 Ctrl+D |
함수 인수 창으로 넣으면 네 칸에 찾을 값 · 표 범위 · 열(행) 번호 · 찾는 방법을 차례로 넣습니다. 수식을 고치는 중이면 F4 로 주소에 $ 를 붙일 수 있습니다(기초 8강).
자주 막히는 곳
- #N/A 가 나온다: 찾을 값이 표의 첫 열에 없음 — 표 범위에 $ 를 빼고 채웠거나, 구간 찾기에 FALSE 를 썼거나, 코드 앞뒤에 빈칸이 있는지 확인
- 엉뚱한 열 값이 나온다: 열 번호는 시트 열(G=7)이 아니라 표 범위 안에서 1, 2, 3 …
- #REF! 가 나온다: 열 번호가 표 범위의 열 수보다 큼
- 오류는 없는데 값이 이상하다: 넷째 인수를 빼먹어 근삿값으로 찾음 — 정렬 안 된 표에서는 FALSE
- 가로 표인데 VLOOKUP 을 썼다: 금액이 한 행에 늘어서 있으면 HLOOKUP
같이 보면 좋은 글
- 엑셀 중급 2강 — COUNTIF · SUMIF 함수(조건 · $ 고정): https://www.coding-now.com/blog/excel/excel-intermediate-02-countif-sumif
- 엑셀 중급 1강 — IF 함수(AND · OR · 중첩 IF): https://www.coding-now.com/blog/excel/excel-intermediate-01-if-function
- 네이버 블로그 cflab 컴활 2급 실기 ② — VLOOKUP · HLOOKUP: https://blog.naver.com/cflab/224431736177
- 네이버 블로그 cflab 컴활 2급 실기 ⑰ — VLOOKUP 함수 사용법: https://blog.naver.com/cflab/224455078787
연습 문제
실습 파일의 '문제' 시트에 세 문제가 있고, '계산작업' 시트의 [표7]~[표9] 답 칸이 비어 있습니다. 풀고 나서 정답 파일과 맞춰 보세요.
- 1문제 7 — C3:C10 이 800 · 500 · 1,500 · 1,200 · 300 · 500 · 800 · 1,500 인지, C10 의 수식 범위가 $E$3:$G$7 그대로인지 확인합니다.
- 2문제 8 — C14:C21 이 0% · 5% · 5% · 10% · 10% · 15% · 15% · 0% 이고, 딱 경계인 100,000(C15) · 500,000(C19)이 위 구간 할인율인지 봅니다.
- 3문제 9 — C25:C32 가 영업팀 · 개발팀 · 총무팀 · 기획팀 · 회계팀 · 인사팀 · 영업팀 · 개발팀인지 확인합니다.
- 4더 해 보기 — 문제 9 식에서 FALSE 를 지워 보고, 오류 없이 틀린 칸을 찾아보세요(정답: C29 가 총무팀).
자주 묻는 질문
Q. VLOOKUP 에서 FALSE 와 TRUE 는 무엇이 다른가요?
FALSE 는 정확히 같은 값만 찾고 없으면 #N/A, TRUE(또는 생략)는 찾을 값 이하에서 가장 큰 값을 찾는 구간 찾기입니다. 이 PC 에서 할인율 구간표에 FALSE 를 쓰자 8칸 중 5칸이 #N/A 였고, 정렬 안 된 부서표에서 넷째 인수를 빼자 5칸이 틀렸습니다.
Q. VLOOKUP 이 #N/A 를 내요.
찾을 값이 표의 첫 열에서 안 찾아진 것입니다. 이 강 문제 7 에서는 표 범위에 $ 없이 아래로 채우자 범위가 한 줄씩 내려가 6칸이 #N/A 였습니다. =VLOOKUP(A3,$E$3:$G$7,3,FALSE) 처럼 표 범위를 고정하세요.
Q. VLOOKUP 과 HLOOKUP 은 언제 쓰나요?
찾는 기준이 표의 첫 열에 세로로 늘어서 있으면 VLOOKUP, 첫 행에 가로로 늘어서 있으면 HLOOKUP 입니다. 이 강 문제 8 의 가로 할인율표에 VLOOKUP 을 쓰자 모두 5% 가 나왔습니다.
Q. 이 문제가 컴활 2급 실기 기출인가요?
아닙니다. 대한상공회의소 문제가 아닌 연습용 자작 문제입니다. 시험의 계산작업 꼴로 문장 · 데이터를 새로 만들었고, 채점 기준 원문은 확인하지 못했습니다.
댓글 0
아직 댓글이 없습니다. 첫 댓글을 남겨 주세요.
댓글은 회원만 쓸 수 있습니다.
광고·욕설·비방·개인정보·불법 내용이 담긴 댓글은 숨기거나 지웁니다. 광고·홍보 글이나 혐오·비방 글을 올린 회원은 강제 탈퇴되고 쓴 글이 모두 지워집니다. 댓글은 닉네임과 함께 공개됩니다. 개인정보처리방침