안녕하세요! 자격증 필기 단기 합격을 향해 오늘도 힘차게 전진하는 수험생 여러분을 응원합니다. 지난 포스팅에서는 엑셀 조건부 계산의 꽃인 [컴활 2급 통계 함수 완벽 가이드: SUMIF, COUNTIF, AVERAGEIF 조건부 함수 3단계 풀이]에 대해 알아보았습니다. 다중 조건(IFS)에서 계산 범위가 맨 앞으로 튀어나오는 규칙이 아직 헷갈리신다면 위 링크를 통해 꼭 먼저 복습하고 오시길 추천해 드립니다.
오늘 함께 부술 2과목 핵심 단원은 필기와 실기를 통틀어 컴활 2급의 ‘알파와 오메가’라고 불리는 컴활 2급 찾기 참조 함수입니다. 방대한 데이터 표에서 내가 원하는 값(예: 사원번호 101번의 기본급)만 쏙쏙 뽑아오는 기능인데요. 특히 VLOOKUP과 HLOOKUP은 시험에 무조건 100% 출제되는 초단골 손님입니다. 인수가 4개나 돼서 처음엔 복잡해 보이지만, 오늘 알려드리는 ‘찾·범·열·옵’ 4단계 주문과 정확히 일치(0) 옵션의 비밀만 깨우치면 어떤 꼬인 문제도 5초 만에 풀어내실 수 있습니다!
1. 컴활 2급 찾기 참조 함수의 핵심: VLOOKUP vs HLOOKUP ⭐⭐
방대한 참조 표(데이터베이스)에서 특정 값을 검색해 그와 같은 줄(행/열)에 있는 다른 데이터를 가져오는 함수입니다. 두 함수는 참조 표가 ‘세로로 길쭉한가’ 아니면 ‘가로로 넙적한가’의 차이만 있을 뿐 구조는 100% 똑같습니다.
| 함수 명칭 | 알파벳 어원 (방향) | 참조 표의 생김새와 추출 방식 |
|---|---|---|
VLOOKUP ⭐ |
Vertical (수직, 세로) | 항목 이름(사번, 이름, 부서 등)이 맨 위쪽(열 머리글)에 있고 데이터가 세로로 길게 내려가는 표. 검색할 값을 첫 번째 열에서 세로(↓)로 내려가며 찾음 |
HLOOKUP |
Horizontal (수평, 가로) | 항목 이름이 맨 왼쪽(행 머리글)에 있고 데이터가 가로로 길게 늘어선 표. 검색할 값을 첫 번째 행에서 가로(→)로 지나가며 찾음 |
💡 4개의 인수 구조 암기 공식: “찾 · 범 · 열(행) · 옵”
VLOOKUP 함수의 기본 문법은 =VLOOKUP(찾을값, 참조범위, 열번호, [옵션])입니다. 순서대로 대입해 볼까요?
- 찾을값: 내가 기준점으로 삼을 값 (예: 내 사원번호 “A101”)
- 참조범위: 정답이 들어있는 거대한 데이터 표 (반드시
$A$1:$D$10처럼 절대참조F4를 눌러 고정해야 함!) ⭐ - 열번호 (HLOOKUP은 행번호): 참조범위 안에서 내가 진짜로 가져오고 싶은 정답(예: 기본급)이 왼쪽에서부터 몇 번째 열에 있는지 적는 숫자 (예: 3번째 열이면
3) - 옵션: 정확히 일치하는 값을 찾을지, 비슷한 값을 찾을지 결정 (아래 2번 항목에서 상세 설명)
2. 합격을 결정짓는 4번째 인수: 옵션 0 (FALSE) vs 1 (TRUE) ⭐⭐⭐
컴활 2급 찾기 참조 함수에서 오답률이 가장 높은 구간입니다. VLOOKUP의 마지막 4번째 자리에 들어가는 숫자에 따라 엑셀이 데이터를 찾는 방식이 완전히 뒤바뀝니다.
| 옵션 값 | 논리값 명칭 | 찾는 방식 및 실전 예시 ⭐ | 사용 조건 (주의사항) |
|---|---|---|---|
0 |
FALSE |
정확히 일치하는 값만 찾음 • 예: 사원번호 “A101”, 제품코드 “TV-01″처럼 고유한 텍스트나 특정 아이디를 찾을 때 |
참조 표가 정렬되어 있지 않아도(뒤죽박죽이어도) 정상적으로 찾아줌 ⭐ |
1 (또는 생략) |
TRUE |
유사 일치 (작거나 같은 값 중 최댓값)를 찾음 • 예: 내 점수가 85점일 때, 표에 85점이 없어도 [80~89점 = B학점] 구간을 스스로 찾아서 B학점을 가져올 때 |
반드시 참조 표의 첫 번째 열이 [오름차순]으로 정렬되어 있어야만 에러가 안 남! ⭐⭐ |
💡 실전 암기 꿀팁: 글자나 이름을 찾을 때는 100% 똑같아야 하므로 무조건 0(FALSE)을 씁니다. 반면, 점수나 구간(~이상 ~미만)을 찾을 때는 표에 내 점수와 똑같은 숫자가 없을 확률이 높으므로 비슷한 값을 찾는 1(TRUE)을 쓴다고 기억하세요!
3. INDEX와 MATCH 함수의 찰떡궁합 콤비 ⭐
VLOOKUP은 무조건 참조 표의 ‘첫 번째 열’에서만 값을 찾아야 한다는 치명적인 단점이 있습니다. 기준값이 표의 중간이나 맨 오른쪽에 있을 때 이를 해결해 주는 구원투수가 바로 INDEX와 MATCH의 결합입니다.
1) 위치를 숫자로 알려주는 MATCH 함수
=MATCH(찾을값, 범위, [옵션])
내가 찾는 값이 지정한 범위에서 몇 번째 위치(몇 번째 칸)에 있는지 숫자로 알려줍니다. (예: “홍길동”이 A1:A5 범위에서 3번째에 있으면 숫자 3을 반환함)
* MATCH의 옵션: 0(정확히 일치), 1(오름차순 유사일치), -1(내림차순 유사일치)
2) 해당 위치의 값을 뽑아오는 INDEX 함수
=INDEX(범위, 행번호, [열번호])
지정한 범위에서 몇 번째 줄(행), 몇 번째 칸(열)에 있는 값을 가져옵니다. (예: =INDEX(A1:C5, 3, 2) → A1:C5 표에서 3행 2열에 있는 데이터를 가져옴)
3) 실전 결합 공식: INDEX(범위, MATCH, 열번호) ⭐
시험에서는 항상 이 둘을 섞어 씁니다. MATCH 함수가 “홍길동은 3번째 줄에 있어!”라고 숫자를 던져주면, INDEX 함수가 “오케이, 그럼 표의 3번째 줄 2번째 칸(기본급)을 가져올게!”하고 콤비 플레이를 하는 원리입니다.
4. 번호에 따라 값을 선택하는 CHOOSE 함수
컴활 2급 찾기 참조 함수 단원에서 가장 직관적이고 쉬운 함수입니다. 주민등록번호 성별 구하기나 요일 구하기 문제에 자주 등장합니다.
=CHOOSE(인덱스번호, 값1, 값2, 값3, ...)
첫 번째 인수인 ‘번호’가 1이면 값1을, 2이면 값2를, 3이면 값3을 선택하여 출력합니다.
- 실전 예시:
=CHOOSE(2, "금", "은", "동")→ 첫 번째 숫자가 2이므로, 두 번째 값인 “은”이 출력됩니다. - 응용 출제:
=CHOOSE(WEEKDAY(A1), "일", "월", "화"...)처럼 요일을 숫자로 바꿔주는WEEKDAY함수와 결합하여 요일 이름을 출력하는 문제가 아주 자주 나옵니다.
5. 컴활 합격 꿀팁: 실전 기출문제 맛보기
오늘 배운 컴활 2급 찾기 참조 함수의 VLOOKUP 옵션과 INDEX/MATCH 원리를 실제 상시 기출문제에 적용해 볼까요?
Q1. 엑셀
VLOOKUP함수에 대한 설명으로 옳지 않은 것은?1) 표의 첫 번째 열에서 특정 값을 찾은 후, 같은 행에 있는 다른 열의 값을 가져오는 함수이다.
2) 네 번째 인수인[Range_lookup]을FALSE(또는 0)로 지정하면 정확하게 일치하는 값을 찾는다.
3) 네 번째 인수를TRUE(또는 1)로 지정하거나 생략하면, 정확하게 일치하는 값이 없을 경우 찾을 값보다 크거나 같은 값 중 가장 작은 값을 찾는다.
4) 유사 일치(TRUE) 옵션을 사용하려면 참조 표의 첫 번째 열이 반드시 오름차순으로 정렬되어 있어야 한다.정답: 3번
(해설: 네 번째 인수를TRUE(1)로 지정했을 때 정확한 값이 없다면, 찾을 값보다 크거나 같은 값이 아니라 ‘작거나 같은 값 중에서 가장 큰 값(최댓값)’을 찾습니다. 예를 들어 내 점수가 85점이고 표에 80점(B)과 90점(A)이 있다면, 85보다 작거나 같은 80점을 찾아 ‘B’학점을 가져오는 원리입니다.)
Q2. [A1:A5] 영역에 순서대로 “바나나, 사과, 포도, 복숭아, 딸기”가 입력되어 있다. 다음 수식
=INDEX(A1:A5, MATCH("포도", A1:A5, 0))의 결과값은 무엇인가?1) 3
2) 포도
3) 사과
4) #N/A정답: 2번
(해설: 안쪽의MATCH("포도", A1:A5, 0)부터 풉니다. “포도”는 범위에서 3번째에 있으므로3을 반환합니다. 그럼 바깥쪽 수식은=INDEX(A1:A5, 3)이 됩니다. A1:A5 범위에서 3번째 행에 있는 값은 다시 “포도”가 되므로 최종 결과는 “포도”가 출력됩니다.)
6. 마무리 및 다음 공부 안내
오늘은 컴활 2급 엑셀 실기에서도 무조건 1번 문제로 등장하는 컴활 2급 찾기 참조 함수에 대해 알아보았습니다. VLOOKUP의 네 번째 옵션(0=정확히 일치, 1=오름차순 유사 일치)과 MATCH(위치 숫자 반환), INDEX(해당 위치 값 반환)의 역할 차이만 정확히 인지하시면 함수 파트의 큰 산을 넘으신 겁니다.
다음 포스팅에서는 이 모든 함수들의 단짝 친구이자, “만약 ~라면 A를, 아니면 B를 출력하라”고 엑셀에게 똑똑한 조건을 부여하는 [컴활 2급 논리 함수 기출 공식: IF, AND, OR 중첩 조건문과 IFERROR 활용 4가지]에 대해 아주 쉽고 명쾌하게 쪼개서 설명해 드리겠습니다. 상시 시험 원서 접수는 대한상공회의소 자격평가사업단 공식 홈페이지에서 지금 바로 확인해 보세요. 오늘도 합격을 향해 달리는 여러분의 빛나는 노력을 응원합니다!