← 엑셀 강의 목차
📗 엑셀 · 중급 3강올린 날 · 10분 읽기

엑셀 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
수식
C3fx
ABCEFG
1[표7] 문구 주문 내역
2제품코드수량단가제품코드제품명단가
3P-10312 P-101볼펜500
4P-10130 P-102노트1,200
5P-1054 P-103형광펜800
6P-10210 P-104지우개300
7P-10425 P-105파일철1,500
8P-10118
9P-1036
10P-1059
1/5제품코드로 오른쪽 제품표(E3:G7)에서 단가를 찾아 C 열에. 단가 C3 을 누르고
따라 하기 — 실습 파일과 같은 주소(장면이 저절로 넘어갑니다)
엑셀 표 [표7] 문구 주문 내역. A 열 제품코드, B 열 수량, C 열 단가에 800, 500, 1,500, 1,200, 300, 500, 800, 1,500 이 들어 있고 빨간 테두리가 있다. 오른쪽 E2:G7 에 제품코드 · 제품명 · 단가 제품표가 있다.
문제 7 결과 — C3:C10(이 PC 엑셀, 범위를 PDF 로 내보낸 그림)
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 에 빨간 테두리가 있다.
$ 없이 채운 결과 — 아래 칸일수록 표가 내려가 6칸 #N/A

$ 가 없으면 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 로 둡니다 — 찾을 값 이하에서 가장 큰 금액을 찾아 그 아래 할인율을 돌려줍니다. 그러려면 첫 행(금액)이 작은 것부터 커지는 순서여야 합니다.

수식
C14fx
ABCEFGHI
12[표8] 고객 구매 금액
13고객구매 금액할인율구매 금액0100,000300,000500,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
1/4할인율표가 가로(13행 금액 · 14행 할인율)라 HLOOKUP. 금액은 그 금액 '이상'부터 — 할인율 C14 를 누르고
따라 하기 — 실습 파일과 같은 주소(장면이 저절로 넘어갑니다)
구매 금액(행)할인율(이 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 에 가로 할인율표가 있다.
문제 8 결과 — C14:C21(이 PC 엑셀)
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 에 빨간 테두리가 있다.
구간 찾기에 FALSE — 표에 딱 있는 금액만 찾아 5칸 #N/A

문제 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 순으로 정렬되어 있지 않다.
넷째 인수 생략 — #N/A 4칸 + D5 가 총무팀으로(오류 없이 틀림)

#N/A 는 눈에 띄지만 D5 → 총무팀처럼 오류 없이 틀린 칸은 놓치기 쉽습니다. 코드 · 이름처럼 '딱 그 값'을 찾을 때는 FALSE, 점수 · 금액 구간을 찾을 때만 TRUE(그때는 표를 오름차순으로). 표 범위에 $ 없이 채운 식은 4칸(27 · 30 · 31 · 32행)이 #N/A 였습니다.

엑셀 표 [표9] 를 수식 보기로 본 모습. C25 부터 C32 까지 =VLOOKUP(A25,$E$25:$F$30,2,FALSE) 가 행 번호만 바뀌며 들어 있고 빨간 테두리가 있다.
수식 보기 — 찾을 값만 A25 → A32, 표 범위 $E$25:$F$30 은 그대로

풀이 순서 한눈에

문제누르는 칸넣는 수식(그대로)그다음
7C3=VLOOKUP(A3,$E$3:$G$7,3,FALSE)Enter → C3:C10 고르고 Ctrl+D
8C14=HLOOKUP(B14,$F$13:$I$14,2,TRUE)Enter → C14:C21 고르고 Ctrl+D
9C25=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. 1문제 7 — C3:C10 이 800 · 500 · 1,500 · 1,200 · 300 · 500 · 800 · 1,500 인지, C10 의 수식 범위가 $E$3:$G$7 그대로인지 확인합니다.
  2. 2문제 8 — C14:C21 이 0% · 5% · 5% · 10% · 10% · 15% · 15% · 0% 이고, 딱 경계인 100,000(C15) · 500,000(C19)이 위 구간 할인율인지 봅니다.
  3. 3문제 9 — C25:C32 가 영업팀 · 개발팀 · 총무팀 · 기획팀 · 회계팀 · 인사팀 · 영업팀 · 개발팀인지 확인합니다.
  4. 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

아직 댓글이 없습니다. 첫 댓글을 남겨 주세요.

댓글은 회원만 쓸 수 있습니다.

광고·욕설·비방·개인정보·불법 내용이 담긴 댓글은 숨기거나 지웁니다. 광고·홍보 글이나 혐오·비방 글을 올린 회원은 강제 탈퇴되고 쓴 글이 모두 지워집니다. 댓글은 닉네임과 함께 공개됩니다. 개인정보처리방침