MOS Expert Excel 자격증 3개월로 끝내기 - 10

 

MOS Excel Expert 자격증 준비 9회차: 고급 수식과 함수 - LOOKUP 함수(VLOOKUP, HLOOKUP, XLOOKUP)

MOS Excel Expert 자격증 준비의 아홉 번째 회차에서는 LOOKUP 함수를 다룹니다. LOOKUP 함수는 데이터를 검색하고 참조하는 데 매우 유용하며, 시험에서 자주 출제되는 핵심 주제입니다. 이번 글에서는 VLOOKUP, HLOOKUP, XLOOKUP의 사용법과 차이점, 다양한 예제를 통해 초보자도 쉽게 이해할 수 있도록 작성했습니다.

1. 학습 목표

• VLOOKUP, HLOOKUP, XLOOKUP 함수의 기본 구문과 사용법을 익힌다.
• 데이터를 검색하고 참조하여 실무에서 활용할 수 있는 기술을 습득한다.

2. 주요 내용

2.1 VLOOKUP 함수

VLOOKUP은 세로 방향으로 데이터를 검색하여 참조 값을 반환하는 함수입니다.

구문

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: 검색할 값(셀 참조 또는 직접 입력).
• table_array: 검색 범위(첫 번째 열이 기준).
• col_index_num: 반환할 값이 있는 열 번호(왼쪽에서 오른쪽으로 번호 매김).
• range_lookup: TRUE(근사값) 또는 FALSE(정확한 값).

반응형

예시 문제 1: VLOOKUP 기본 사용

문제: 아래 데이터에서 ID를 기준으로 이름을 검색하세요.

ID	이름	부서
101	김철수	영업팀
102	이영희	마케팅팀
103	박민수	개발팀

 

풀이:

1. ID가 입력된 셀(A5)에 102를 입력합니다.
2. 이름을 반환할 셀(B5)에 아래 수식을 입력합니다.
    

=VLOOKUP(A5, A2:C4, 2, FALSE)

결과: ID가 102일 때 이름은 이영희입니다.

2.2 HLOOKUP 함수

HLOOKUP은 가로 방향으로 데이터를 검색하여 참조 값을 반환하는 함수입니다.

구문

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: 검색할 값(셀 참조 또는 직접 입력).
• table_array: 검색 범위(첫 번째 행이 기준).
• row_index_num: 반환할 값이 있는 행 번호(위에서 아래로 번호 매김).
• range_lookup: TRUE(근사값) 또는 FALSE(정확한 값).

예시 문제 2: HLOOKUP 기본 사용

문제: 아래 데이터에서 "제품 B"의 판매량을 검색하세요.

제품명	제품A	제품B	제품C
판매량	100	200	300

 

풀이:

1. 제품명이 입력된 셀(A5)에 "제품 B"를 입력합니다.
2. 판매량을 반환할 셀(B5)에 아래 수식을 입력합니다.
    

=HLOOKUP(A5, A1:D2, 2, FALSE)

결과: "제품 B"의 판매량은 200입니다.

728x90

2.3 XLOOKUP 함수

XLOOKUP은 VLOOKUP과 HLOOKUP의 한계를 극복한 최신 함수로, 데이터의 위치와 관계없이 값을 검색하고 반환할 수 있습니다.

구문

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: 검색할 값.
• lookup_array: 검색 범위.
• return_array: 반환할 값이 있는 범위.
• [if_not_found]: 검색 결과가 없을 때 표시할 값(선택 사항).
• [match_mode]: 정확히 일치(-1/1), 근사값 또는 와일드카드 매칭(선택 사항).
• [search_mode]: 검색 방향 설정(선택 사항).

예시 문제 3: XLOOKUP 기본 사용

문제: 아래 데이터에서 ID를 기준으로 부서를 검색하세요.

ID	이름	부서
101	김철수	영업팀
102	이영희	마케팅팀
103	박민수	개발팀

 

풀이:

1. ID가 입력된 셀(A5)에 103을 입력합니다.
2. 부서를 반환할 셀(B5)에 아래 수식을 입력합니다.
    

=XLOOKUP(A5, A2:A4, C2:C4)

결과: ID가 103일 때 부서는 개발팀입니다.

SMALL

예제 추가

예시 문제 4: VLOOKUP과 XLOOKUP 비교

문제: 아래 데이터에서 "제품 B"의 가격을 검색하세요.

제품명	가격
제품 A	₩10,000
제품 B	₩20,000
제품 C	₩30,000

 

풀이 (VLOOKUP):

1. 제품명이 입력된 셀(A5)에 "제품 B"를 입력합니다.
2. 가격을 반환할 셀(B5)에 아래 수식을 입력합니다.
    

=VLOOKUP(A5, A2:B4, 2, FALSE)

결과: "제품 B"의 가격은 ₩20,000입니다.

풀이 (XLOOKUP):

1. 동일한 조건으로 아래 수식을 사용합니다.
    

=XLOOKUP(A5, A2:A4, B2:B4)

결과: "제품 B"의 가격은 ₩20,000입니다.

예시 문제 5: XLOOKUP 고급 사용

문제: 아래 데이터에서 "서울" 지역의 매출액을 검색하고 없으면 "데이터 없음"이라는 메시지를 표시하세요.

지역	매출액
부산	₩500K
대구	₩300K

 

풀이:

1. 지역명이 입력된 셀(A5)에 "서울"을 입력합니다.
2. 매출액을 반환할 셀(B5)에 아래 수식을 입력합니다.
    

=XLOOKUP(A5, A2:A3, B2:B3, "데이터 없음")

결과: "서울" 지역은 없으므로 "데이터 없음"이 표시됩니다.

3. 추가 팁

VLOOKUP과 HLOOKUP 한계

• VLOOKUP은 항상 첫 번째 열만 기준으로 검색하며 왼쪽 방향 조회가 불가능합니다.
• HLOOKUP은 첫 번째 행만 기준으로 작동하며 위쪽 방향 조회가 불가능합니다.

XLOOKUP 장점

• 데이터 위치에 관계없이 자유롭게 조회 가능.
• 정확히 일치하지 않는 경우 근사값 또는 사용자 정의 메시지 표시 가능.
• 최신 Excel 버전에서만 사용 가능하므로 호환성을 확인하세요.