엑셀 검색/참조 함수 정리

엑셀과 씨름하다 탈모 오는 줄 아셨죠? 복잡한 데이터 속에서 원하는 정보를 찾느라 밤새 엑셀 창과 씨름하는 여러분들을 위해 준비했습니다! 바로 엑셀의 꽃, 검색/참조 함수 활용법 마스터 클래스! VLOOKUP, INDEX & MATCH, HLOOKUP… 이름만 들어도 머리 아프시다고요? 걱정 마세요!

이 포스팅에서는 VLOOKUP 함수 활용법부터 INDEX & MATCH 함수 조합, 그리고 HLOOKUP 함수 사용 가이드까지, 복잡한 함수들을 쉽고 재밌게 풀어드립니다. 마지막에는 상황별 최적의 함수를 고르는 엑셀 검색 함수 선택 팁까지 알려드리니, 이제 엑셀 앞에서 눈물 훔치는 일은 없을 겁니다. 자, 이제 엑셀 마스터로 변신할 준비되셨나요?

예제파일은 아래 내려보시면 있으니 다운받아서 활용해보세요.

 

 

VLOOKUP 함수 활용법

드디어, 엑셀의 핵심 기능 중 하나인 VLOOKUP 함수에 대해 알아볼 시간이에요! 마치 숨겨진 보물을 찾는 것처럼, 원하는 데이터를 아주 쉽고 빠르게 찾아낼 수 있도록 도와주는 마법같은 함수랍니다! 자, 그럼 이 마법 지팡이(VLOOKUP)를 어떻게 휘두르는지 한번 살펴볼까요~? ^^

VLOOKUP은 Vertical Lookup의 약자로, 세로로 된 표에서 특정 값을 찾아, 그 값과 연결된 다른 값을 반환하는 함수입니다. "수직 검색"이라고 생각하시면 좀 더 이해하기 쉬우실 거예요! 마치 도서관에서 책 제목(찾는 값)을 알고, 그 책의 저자(반환 값)를 찾는 것과 비슷하죠. 책 제목이 적힌 목록을 세로로 쭉 훑어보면서 찾는 것처럼 말이에요!

VLOOKUP 함수의 기본 구조

VLOOKUP 함수의 기본 구조는 다음과 같습니다. 꼭 기억해 두세요!

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

복잡해 보인다고요? 전혀 그렇지 않아요! 하나씩 뜯어보면 아주 간단하답니다.

• lookup_value: 찾고자 하는 값입니다. 예를 들어, 위 도서관 비유에서 "책 제목"에 해당하죠. 이 값은 숫자, 텍스트, 또는 셀 참조가 될 수 있습니다. 다양한 형태로 변신 가능한 만능 엔터테이너 같죠?!
• table_array: lookup_value를 찾을 범위입니다. 도서관의 "책 목록"과 같아요. 이 범위는 반드시 찾고자 하는 값이 포함된 열을 첫 번째 열로 포함해야 합니다. 잊지 마세요! 첫 번째 열이 제일 중요해요!!
• col_index_num: 반환할 값이 있는 열의 번호입니다. "저자" 정보가 있는 열 번호라고 생각하시면 됩니다. table_array의 첫 번째 열은 1, 두 번째 열은 2, 이런 식으로 번호가 매겨집니다. 참 쉽죠?
• [range_lookup]: 선택 사항이지만, 아주 중요한 부분입니다! TRUE(또는 1)로 설정하면 유사 일치를, FALSE(또는 0)로 설정하면 정확히 일치하는 값을 찾습니다. 대소문자는 구분하지 않아요. 만약 생략하면 기본값은 TRUE(유사 일치)가 됩니다. 정확한 값을 찾고 싶다면 꼭 FALSE를 입력해야 한다는 것을 명심하세요?!?!?

자, 이제 실제 예시를 통해 VLOOKUP 함수의 활용법을 좀 더 자세히 알아보도록 하겠습니다.

상황 1: 제품 코드를 이용하여 제품 가격 찾기

제품 코드와 가격이 나열된 표가 있다고 가정해 볼게요. 제품 코드 "A123"의 가격을 찾고 싶다면 어떻게 해야 할까요?

| 제품 코드 | 제품 가격 |
|---|---|
| A123 | 10,000 |
| B456 | 20,000 |
| C789 | 30,000 |

이 경우 VLOOKUP 함수는 다음과 같이 사용할 수 있습니다.

=VLOOKUP("A123", A1:B3, 2, FALSE)

• lookup_value: "A123" (찾고자 하는 제품 코드)
• table_array: A1:B3 (제품 코드와 가격이 있는 범위)
• col_index_num: 2 (가격이 있는 두 번째 열)
• range_lookup: FALSE (정확히 일치하는 값 찾기)

이 함수를 실행하면 결과값으로 10,000이 반환됩니다. 참 신기하죠?!

상황 2: 학생 번호를 이용하여 학생 이름 찾기

이번에는 학생 번호와 이름이 있는 표에서 특정 학생의 이름을 찾아볼까요? 학생 번호가 202301인 학생의 이름을 찾는다고 가정해 보겠습니다.

| 학생 번호 | 학생 이름 |
|---|---|
| 202301 | 김철수 |
| 202302 | 박영희 |
| 202303 | 이민지 |

이 경우 VLOOKUP 함수는 다음과 같이 사용할 수 있습니다.

=VLOOKUP(202301, A1:B3, 2, FALSE)

• lookup_value: 202301 (찾고자 하는 학생 번호)
• table_array: A1:B3 (학생 번호와 이름이 있는 범위)
• col_index_num: 2 (이름이 있는 두 번째 열)
• range_lookup: FALSE (정확히 일치하는 값 찾기)

이 함수를 실행하면 결과값으로 "김철수"가 반환됩니다. VLOOKUP 함수, 정말 만능이죠?! 이처럼 VLOOKUP 함수는 다양한 상황에서 원하는 데이터를 빠르고 정확하게 찾아낼 수 있도록 도와줍니다. 엑셀 작업의 효율성을 높여주는 필수 함수라고 할 수 있죠! 앞으로 자주 사용하게 될 거예요! 다음에는 INDEX & MATCH 함수 조합에 대해 알아보도록 하겠습니다! 기대해 주세요~

 

각 함수 사용법은 예제 파일을 다운로드해서
👇👇👇직접 변경해가면서 사용해보세요👇👇👇

반응형

검색_참조 함수 예시.xlsx

0.01MB

INDEX & MATCH 함수 조합

VLOOKUP 함수만 주구장창 쓰시다가 답답함을 느끼셨나요?! 드디어 VLOOKUP의 한계를 뛰어넘는, 마치 날개 단 것처럼 자유로운 함수 조합의 세계에 오신 것을 환영합니다! (짝짝짝!) 바로 INDEX & MATCH 함수의 콜라보레이션! 마법같은 이 조합은 엑셀의 신세계를 열어드릴 겁니다. 자, 이제 엑셀 시트를 휘어잡는 INDEX & MATCH 듀오의 현란한 퍼포먼스를 감상해보실까요?

INDEX 함수

자, 먼저 INDEX 함수부터 간단히 살펴볼게요. INDEX 함수는 마치 도서관 사서처럼, 특정 범위에서 지정한 행과 열에 위치한 값을 쏙! 뽑아줍니다. 예를 들어, INDEX(A1:C10, 3, 2)는 A1:C10 범위에서 3번째 행, 2번째 열에 있는 값, 즉 B3 셀의 값을 가져온다는 뜻이죠! 참 쉽죠~잉?!

MATCH 함수

그럼 이번엔 MATCH 함수! MATCH 함수는 "찾았다!"를 외치는 탐정과 같습니다. 특정 값이 어느 위치에 있는지 행 또는 열 번호로 알려주는 역할을 하죠. 예를 들어, MATCH("사과", A1:A10, 0)는 A1:A10 범위에서 "사과"라는 값이 몇 번째 행에 있는지 찾아 그 행 번호를 반환합니다. 0은 정확히 일치하는 값을 찾으라는 옵션! 이 부분 밑줄 쫙! 별표 땡땡!

INDEX & MATCH 함수의 조합

이제 두 함수의 콜라보가 시작됩니다! MATCH 함수가 찾아낸 위치 정보를 INDEX 함수에 전달하면, INDEX 함수는 정확하게 원하는 값을 가져오는 것이죠! 마치 첩보 작전처럼 말이죠! 😎

예를 들어, 제품 코드를 이용하여 제품 가격을 찾는다고 가정해 봅시다. 제품 코드는 A열에, 제품 가격은 C열에 있다고 할 때, =INDEX(C:C, MATCH("A001", A:A, 0)) 이렇게 수식을 입력하면, "A001" 제품 코드에 해당하는 가격을 C열에서 찾아서 보여줍니다!

VLOOKUP 함수와의 비교

VLOOKUP 함수는 왼쪽에서 오른쪽으로만 검색이 가능했지만, INDEX & MATCH 조합은 좌우 상관없이, 심지어 위아래로도 자유롭게 검색이 가능합니다! 이 얼마나 놀라운 유연성인가요?! VLOOKUP 함수를 쓸 때 겪었던 컬럼 위치 변경의 스트레스는 이제 안녕~! 👋

다중 조건 검색

더 나아가, INDEX & MATCH 함수 조합은 다중 조건 검색도 가능하게 해줍니다. 예를 들어, 제품 코드와 색상을 모두 만족하는 제품의 가격을 찾고 싶다면?! MATCH 함수 안에 또 다른 MATCH 함수를 넣어 다중 조건을 설정할 수 있죠. 마치 엑셀 함수계의 인셉션 같지 않나요?! 🤯

복잡한 예시

자, 그럼 좀 더 복잡한 예시를 볼까요? 제품 코드가 A열에, 색상이 B열에, 사이즈가 C열에, 가격이 D열에 있다고 가정하고, "A001" 제품 코드, "빨강" 색상, "L" 사이즈에 해당하는 제품의 가격을 찾고 싶다면?! 다음과 같은 수식을 사용할 수 있습니다.

=INDEX(D:D, MATCH(1, (A:A="A001")*(B:B="빨강")*(C:C="L"), 0))

여기서 핵심은 (A:A="A001")*(B:B="빨강")*(C:C="L") 부분! 각 조건을 만족하는 행에는 1, 만족하지 않는 행에는 0이 입력되고, 이 값들을 곱해서 최종적으로 모든 조건을 만족하는 행에만 1이 남게 됩니다. 그래서 해당 제품 가격을 알 수 있죠.

중요한것은 수식을 완성하고 그냥 enter를 누르지 마시고 꼭 "Ctrl + Shift + Enter"를 눌러주셔야 실행이 됩니다.

MATCH 함수는 이 1을 찾아서 해당 행 번호를 INDEX 함수에 전달하는 것이죠! 정말 기발하지 않나요?! 🤩

INDEX & MATCH 함수의 장점

물론, 처음에는 조금 복잡해 보일 수 있습니다. 하지만 몇 번 연습하다 보면 금방 익숙해질 거예요! INDEX & MATCH 함수 조합은 엑셀 실력을 한 단계 업그레이드 시켜줄 강력한 무기입니다! 이제 VLOOKUP 함수는 잊고, INDEX & MATCH 함수의 매력에 푹 빠져보세요! 😉

대용량 데이터 처리 속도

더 나아가, 대용량 데이터를 다룰 때 INDEX & MATCH 함수 조합은 VLOOKUP 함수보다 훨씬 빠른 속도를 자랑합니다. VLOOKUP 함수는 전체 표를 검색하는 반면, INDEX & MATCH 함수는 필요한 열만 검색하기 때문이죠! 데이터 처리 속도가 답답해서 머리카락을 쥐어뜯던 과거는 이제 안녕~! INDEX & MATCH 함수와 함께라면 빛의 속도로 데이터를 처리할 수 있습니다! 🚀

자, 이제 INDEX & MATCH 함수 조합의 강력함을 충분히 느끼셨나요? 엑셀의 세계는 넓고, 배울 것은 많습니다! 끊임없이 배우고 익혀서 엑셀 마스터가 되는 그날까지! 화이팅! 💪

 

HLOOKUP 함수 사용 가이드

VLOOKUP 함수가 세로 방향 검색이라면, HLOOKUP은 가로 방향 검색의 달인이라고 할 수 있죠! 마치 워드 서치 퍼즐에서 가로로 단어를 찾는 것과 비슷하다고 생각하시면 됩니다. ^^ VLOOKUP이 왼쪽 첫 번째 열을 기준으로 값을 찾는다면, HLOOKUP은 맨 위 첫 번째 행을 기준으로 값을 찾는다는 점! 이것만 기억하면 HLOOKUP 함수 정복은 식은 죽 먹기입니다?!

자, 그럼 이 매력적인 HLOOKUP 함수, 어떻게 사용하는지 자세히 알아볼까요? HLOOKUP 함수의 구성은 VLOOKUP과 놀라울 정도로 닮았답니다. 마치 쌍둥이처럼요! 함수의 형태는 HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) 입니다. 뭐가 뭔지 하나하나 뜯어보도록 하죠!

HLOOKUP 함수의 인수

1. `lookup_value`: 찾고자 하는 값입니다. 예를 들어, 제품 코드 "A1234"의 가격을 알고 싶다면 "A1234"가 바로 lookup_value가 됩니다. 문자열일 경우 따옴표("")로 감싸주는 센스! 잊지 마세요~?

2. `table_array`: 찾고자 하는 값이 포함된 표의 범위입니다. 데이터가 A1:E10 범위에 있다면 A1:E10라고 입력하면 됩니다. 표 범위 설정, 정말 중요해요!! 잘못 설정하면 원하는 결과를 얻을 수 없으니 주의 또 주의!

3. `row_index_num`: 찾고자 하는 값이 있는 행 번호입니다. table_array에서 몇 번째 행에 있는 값을 가져올지 지정하는 거죠. 만약 table_array가 A1:E10이고, A1:E10에서 3번째 행에 있는 값을 가져오고 싶다면 row_index_num에 3을 입력하면 됩니다. 참 쉽죠잉?!

4. `[range_lookup]`: 이 부분은 선택 사항입니다! TRUE(또는 1)를 입력하면 유사 일치 검색, FALSE(또는 0)를 입력하면 정확히 일치하는 값 검색을 합니다. 정확한 값을 찾고 싶다면 FALSE를 입력하는 것이 좋습니다! 괜히 유사 일치로 엉뚱한 값 가져오면 곤란하잖아요~?! 대부분의 경우 FALSE를 사용한다는 사실! 기억해 두세요! (물론 상황에 따라 TRUE를 사용해야 할 때도 있답니다!)

HLOOKUP 함수 사용 예시

자, 이제 실제 예시를 통해 HLOOKUP 함수를 사용해 보겠습니다. 아래와 같은 표가 있다고 가정해 봅시다.

제품코드	A1234	B5678	C9012	D3456	E7890
가격	10,000	20,000	12,200	11,100	13,000
재고	100	200	300	150	200
판매량	30	40	60	100	90

제품 코드 "B5678"의 재고를 알고 싶다면 어떻게 해야 할까요? 바로 HLOOKUP 함수를 사용하면 됩니다! =HLOOKUP("B5678", A1:D5, 3, FALSE) 이렇게 입력하면 "B5678"에 해당하는 재고량인 "200"이라는 값을 가져올 수 있습니다.

• "B5678"은 lookup_value, 즉 찾고자 하는 값입니다.
• A1:D5는 table_array, 데이터가 있는 표의 범위입니다.
• 3은 row_index_num, 재고가 있는 행 번호입니다. (첫 번째 행이 제품 코드, 두 번째 행이 가격, 세 번째 행이 재고이므로 3입니다.)
• FALSE는 range_lookup, 정확히 일치하는 값을 찾기 위해 사용합니다.

어때요, 참 쉽죠?! HLOOKUP 함수, 이제 어렵지 않게 사용할 수 있겠죠? ^^ VLOOKUP과 함께 HLOOKUP 함수까지 마스터하면 엑셀 데이터 분석 능력이 쑥쑥 향상될 겁니다! 데이터 분석의 고수를 꿈꾸신다면 HLOOKUP 함수, 절대 놓치지 마세요!

HLOOKUP 함수 활용 예시

좀 더 복잡한 예시를 들어볼까요? 만약 위의 표에서 제품 코드가 "C9012"인 제품의 판매량과 재고의 합을 구하고 싶다면 어떻게 해야 할까요? 이럴 때도 HLOOKUP 함수를 활용할 수 있습니다! 다음과 같은 수식을 사용하면 됩니다.

=HLOOKUP("C9012", A1:D5, 3, FALSE) + HLOOKUP("C9012", A1:D5, 4, FALSE)

이 수식은 "C9012" 제품의 재고(300)와 판매량(150)을 각각 HLOOKUP 함수로 가져와서 더하는 것입니다. 결과적으로 450이라는 값을 얻을 수 있죠!

HLOOKUP 함수는 SUM, AVERAGE, IF 등 다른 함수와 결합하여 더욱 강력한 분석 도구로 활용될 수 있습니다. 예를 들어, 특정 제품의 가격이 20,000원 이상이면 "고가", 그렇지 않으면 "저가"로 표시하고 싶다면 다음과 같은 수식을 사용할 수 있습니다.

=IF(HLOOKUP("B5678", A1:D5, 2, FALSE)>=20000, "고가", "저가")

이처럼 HLOOKUP 함수는 다양한 상황에서 유용하게 활용될 수 있습니다! 여러분도 HLOOKUP 함수를 적극 활용하여 엑셀 마스터가 되어 보세요! 데이터 분석, 이제 어렵지 않아요! HLOOKUP 함수와 함께라면! ^^

자, 이제 여러분은 HLOOKUP 함수의 기본적인 사용법부터 응용까지 모두 마스터하셨습니다! 실제 데이터에 적용해보면서 HLOOKUP 함수의 매력에 푹 빠져보세요! 데이터 분석의 세계가 여러분을 기다리고 있습니다! Let's get it!

 

엑셀 검색 함수 선택 팁

드디어, 대망의 엑셀 검색 함수 선택 팁 시간입니다! 마치 미로 같은 엑셀 함수의 세계에서 길을 잃으셨나요? VLOOKUP, INDEX & MATCH, HLOOKUP… 도대체 뭘 써야 할지 감도 안 잡히시죠? 걱정 마세요! 여러분의 엑셀 고민, 제가 시원하게 해결해 드리겠습니다! 마법처럼 딱 맞는 함수를 찾아주는 마법 지팡이는 없지만 (있으면 제가 먼저 썼겠죠?!^^), 최적의 함수를 선택하는 비법, 지금부터 대방출합니다!

자, 먼저 상황별 함수 선택 전략부터 알아볼까요? 마치 RPG 게임 캐릭터 선택처럼, 각 함수마다 특징과 장단점이 있습니다. 상황에 맞는 함수를 골라야 퀘스트(데이터 분석)를 성공적으로 완료할 수 있답니다!

함수별 특징과 장단점

1. VLOOKUP: 단일 열 검색의 제왕! 데이터가 정렬되어 있지 않아도, 특정 열에서 원하는 값을 찾아 다른 열의 해당 값을 뽑아내는 데 탁월합니다. 마치 숨바꼭질의 달인 같죠? 하지만, 검색 열이 왼쪽에 고정되어 있다는 슬픈(?) 제약이 있습니다. 검색하려는 열이 왼쪽에 있다면 VLOOKUP을 사용하세요! 성공률 99.9%를 자랑합니다! (0.1%는… 엑셀 버그?! ㅎㅎ)

2. INDEX & MATCH: 콤비 플레이의 진수! INDEX 함수는 특정 위치의 값을 가져오는 능력자, MATCH 함수는 특정 값의 위치를 찾는 탐정입니다. 이 둘이 힘을 합치면? 원하는 값을 어떤 열에서든 자유자재로 찾아낼 수 있는 최강 콤비가 탄생합니다! VLOOKUP의 검색 열 제약을 멋지게 극복하는 콤비네이션이죠! 데이터가 복잡하고, 여러 조건으로 검색해야 할 때, INDEX & MATCH 콤비를 소환하세요!

3. HLOOKUP: VLOOKUP의 쌍둥이 동생! VLOOKUP이 세로 방향 검색이라면, HLOOKUP은 가로 방향 검색의 전문가입니다! 행에서 원하는 값을 찾아 다른 행의 해당 값을 가져오는 데 탁월합니다. 마치 지도에서 특정 위도를 찾아 경도를 확인하는 것과 같죠. 하지만 VLOOKUP과 마찬가지로 검색 행이 위쪽에 고정되어 있다는 점, 기억하세요! 데이터가 행으로 구성되어 있고, 검색하려는 행이 가장 위에 있다면 HLOOKUP을 추천합니다!

4. CHOOSE: 인덱스 번호로 값을 선택하는 마법사! CHOOSE 함수는 마치 옷장에서 옷을 고르듯, 인덱스 번호를 사용하여 목록에서 특정 값을 선택합니다. 예를 들어, 1번은 빨간색, 2번은 파란색, 3번은 초록색처럼 말이죠! 1부터 254개까지의 값을 처리할 수 있다는 놀라운 능력을 가지고 있습니다! 인덱스 번호를 기준으로 값을 선택해야 한다면, CHOOSE 함수가 정답입니다!

5. XLOOKUP: 미래에서 온 검색 함수! 엑셀의 최신 버전에서 등장한 XLOOKUP은 마치 만능 검색 엔진처럼, 이전 함수들의 장점만 모아놓은 궁극의 함수입니다?! 검색 방향, 일치 유형, 반환 값 등을 자유롭게 설정할 수 있어, 복잡한 검색 조건도 거뜬히 처리할 수 있죠! 마치 엑셀계의 인공지능 같다고나 할까요? ^^ 엑셀 최신 버전을 사용한다면, XLOOKUP을 적극 활용해 보세요! 새로운 가능성의 세계가 열립니다!

실전 예제

자, 이제 실전 예제를 통해 각 함수의 활용법을 더 자세히 알아볼까요?

• 예제 1: 제품 코드로 제품명 찾기 (VLOOKUP 활용)
  제품 코드 목록과 제품명 목록이 있습니다. 제품 코드를 입력하면 해당 제품명을 자동으로 표시하고 싶다면? VLOOKUP 함수를 사용하세요!
• 예제 2: 여러 조건으로 판매량 찾기 (INDEX & MATCH 활용)
  제품명, 지역, 날짜별 판매량 데이터가 있습니다. 특정 제품, 특정 지역, 특정 날짜의 판매량을 찾고 싶다면? INDEX & MATCH 콤비를 활용하세요!
• 예제 3: 월별 매출액 찾기 (HLOOKUP 활용)
  월별 매출액 데이터가 행으로 정렬되어 있습니다. 특정 월의 매출액을 찾고 싶다면? HLOOKUP 함수가 딱입니다!
• 예제 4: 순위에 따른 등급 부여 (CHOOSE 활용)
  학생들의 순위에 따라 등급 (1등: A+, 2등: A0, …) 을 부여하고 싶다면? CHOOSE 함수를 사용하면 간단하게 해결할 수 있습니다!
• 예제 5: 복잡한 조건의 데이터 검색 (XLOOKUP 활용)
  여러 조건을 만족하는 데이터를 찾아야 하거나, 검색 결과가 없을 경우 특정 메시지를 표시하고 싶다면? XLOOKUP 함수를 활용해 보세요!

함수 선택 팁 정리

• 검색 방향: 세로 방향 검색이면 VLOOKUP, 가로 방향 검색이면 HLOOKUP!
• 검색 열/행 위치: 검색 열/행이 고정되어 있지 않다면 INDEX & MATCH!
• 인덱스 번호로 값 선택: CHOOSE 함수!
• 엑셀 최신 버전 사용자: XLOOKUP 함수 강력 추천!

이제 엑셀 검색 함수 선택, 더 이상 고민하지 마세요! 여러분의 엑셀 실력이 한 단계 업그레이드될 것을 보장합니다! 다음에는 더욱 유용한 엑셀 팁으로 찾아뵙겠습니다! (찡긋😉)

 

자, 이제 VLOOKUP, INDEX & MATCH, HLOOKUP까지 엑셀계의 어벤져스급 검색/참조 함수들을 모두 만나보셨습니다! 마치 엑셀 시트 안에서 숨바꼭질하는 데이터도 이제 꼼짝 못 하겠죠? 함수 하나씩 알아갈 때마다 엑셀 고수의 향기가 솔솔 나지 않나요? 어떤 함수를 써야 할지 헷갈린다고요? 걱정 마세요! 선택 팁까지 챙겨드렸으니 이제 엑셀 앞에서 당당하게 어깨 펴고 데이터를 주무르세요! 다음엔 더욱 강력한 엑셀 팁으로 돌아오겠습니다. 그때까지 엑셀과 함께 행복한 시간 보내세요! 😉