VLOOKUP 이젠 안녕! 비개발자 루카의 XLOOKUP & 동적 배열 탐구생활

안녕하세요, 코딩은 못 하지만 IT가 너무 궁금한 비개발자 루카(Luka)입니다.

저는 주로 IT 서비스 기획이나 운영 업무를 하면서 수많은 데이터를 엑셀로 다루곤 합니다. 그러다 보니 자연스럽게 엑셀 함수들과 친해질 수밖에 없었는데요. 특히 VLOOKUP은 제 업무에서 없어서는 안 될 존재였습니다. 그런데 어느 날, 팀원 한 명이 "루카님, 아직도 VLOOKUP 쓰세요? XLOOKUP이랑 동적 배열 써보세요!"라고 하는 겁니다. 개발자는 아니지만, 새로운 기술이나 기능에는 늘 호기심을 참지 못하는 저! 'XLOOKUP? 동적 배열? 이게 뭔데?' 궁금증이 폭발해서 바로 파헤치기 시작했습니다. 그리고 결론부터 말씀드리자면, VLOOKUP을 쓰던 제가 얼마나 비효율적이었는지 깨달았습니다. 오늘은 그 탐구 기록을 여러분과 공유하려고 합니다!

VLOOKUP, 이젠 보내줄 때가 됐나? (루카의 고민)

솔직히 말씀드리면, VLOOKUP은 참 좋은 친구였습니다. 수십, 수백 개의 데이터를 매칭할 때 이만한 게 없었죠. 하지만 늘 불편한 점들이 있었습니다.

1. 오른쪽으로만 찾는다: 이게 제일 답답했어요. 특정 값을 찾아서 그 왼쪽에 있는 데이터를 가져와야 할 때, 어쩔 수 없이 데이터를 복사해서 열 순서를 바꾸거나 INDEX MATCH 같은 더 복잡한 함수 조합을 써야 했죠. 왠지 모르게 비효율적이란 생각에서 벗어날 수 없었습니다.
2. 열 번호 지정: 데이터 중간에 열이 추가되거나 삭제되면, VLOOKUP 함수에 입력했던 열 번호를 일일이 수정해야 했습니다. 제 실수로 한두 개 빠뜨리면 엉뚱한 값이 나와서 데이터 신뢰도가 떨어지곤 했죠.
3. 정확한 값 찾기가 기본이 아니다?: VLOOKUP의 마지막 인수인 TRUE (유사 일치)와 FALSE (정확히 일치) 중 TRUE가 기본값이라는 걸 아셨나요? 저는 대부분 정확히 일치(FALSE)를 썼는데, 깜빡하고 안 쓰면 엉뚱한 결과가 나와서 애먹었던 기억이 있습니다.
4. #N/A의 향연: 찾는 값이 없을 때마다 #N/A 에러가 뜨는 건 정말 익숙한 풍경이었죠. 이걸 가리려면 늘 IFERROR 함수를 앞에 붙여야 했습니다.

이런 소소한(?) 불편함들이 쌓이다 보니, '더 좋은 방법이 없을까?' 하는 고민을 하던 찰나에 XLOOKUP과 동적 배열의 존재를 알게 된 겁니다.

XLOOKUP, 너 대체 뭐니? (feat. VLOOKUP과의 한 판 승부)

XLOOKUP은 엑셀 365 버전에서 등장한, VLOOKUP의 모든 단점을 보완하고 장점을 강화한 '차세대 조회 함수'라고 감히 말씀드릴 수 있습니다. 제가 직접 써보고 느낀 강력한 장점들을 공유합니다.

1. 직관적인 사용법과 강력한 유연성

VLOOKUP은 (찾을 값, 범위, 열 번호, [옵션])으로 이루어져 있었죠? XLOOKUP은 훨씬 직관적입니다.

=XLOOKUP(찾을_값, 찾을_범위, 반환_범위, [if_not_found], [match_mode], [search_mode])

저처럼 비개발자 입장에서는 찾을_범위와 반환_범위를 따로 지정하는 게 너무 편했습니다. 열 번호를 셀 필요도 없고, 왼쪽이든 오른쪽이든 범위만 지정하면 끝이니까요! 공식 문서를 찾아보니, 이 구조 덕분에 데이터 중간에 열이 추가되거나 삭제되어도 함수를 수정할 필요가 없어서 유지보수성이 VLOOKUP보다 압도적으로 좋다고 하더군요.

2. 기본값이 '정확히 일치', 그리고 에러 처리까지 한 방에!

앞서 VLOOKUP의 FALSE를 깜빡하면 생긴다고 했던 문제, XLOOKUP은 정확히 일치(0)가 기본값입니다. 휴, 얼마나 마음이 편한지 모릅니다. 그리고 찾는 값이 없을 때 나타나던 #N/A 에러도 [if_not_found] 인수에 미리 지정해둘 수 있습니다. 예를 들어, "" (공백)이나 "해당 없음" 같은 메시지를 넣어두면 IFERROR를 따로 쓸 필요가 없어집니다. 함수가 훨씬 간결해지죠.

3. 성능 향상? 대규모 데이터셋에서 빛을 발하다!

비개발자인 제가 코드를 뜯어본 건 아니지만, 관련 포럼이나 기술 블로그를 찾아보니 XLOOKUP은 내부적으로 VLOOKUP보다 훨씬 효율적인 방식으로 데이터를 처리한다고 합니다. 특히, [search_mode] 인수를 활용해서 데이터가 미리 정렬되어 있을 때 '이진 탐색'(binary search) 모드(예: -2 또는 2)를 사용하면, 10만 행 이상의 대규모 데이터셋에서 VLOOKUP 대비 최대 2~3배까지 계산 속도가 향상될 수 있다고 해요. 제가 겪었던 경험에 비춰보면, 예전에는 10만 행짜리 데이터 두 개를 VLOOKUP으로 매칭하면 엑셀이 몇 초씩 멈추곤 했는데, XLOOKUP으로는 훨씬 매끄럽게 돌아갔습니다. 물론 제 환경에서 직접 초 단위로 비교한 건 아니지만, 체감상으로도 확연히 빨라진 느낌이었죠. VLOOKUP이 전체 범위를 스캔하는 반면, XLOOKUP은 지정된 검색/반환 범위만 참조하기 때문에 메모리 효율성도 좋다고 합니다.

XLOOKUP vs. VLOOKUP 핵심 비교 (루카의 정리)

특징	VLOOKUP	XLOOKUP	루카의 한 마디!
검색 방향	오른쪽으로만 가능	왼쪽/오른쪽 모두 가능	왼쪽도 가능! 이젠 데이터 순서 안 바꿔도 됩니다!
열 참조	열 번호 지정 (예: 2, 3)	반환할 열 범위를 직접 지정 (예: B:B, C:C)	열 추가/삭제에 자유롭습니다!
기본 매칭	유사 일치 (TRUE)	정확히 일치 (0)	에러 걱정 덜었습니다!
에러 처리	IFERROR 함수 별도 사용	[if_not_found] 인수로 내장	함수가 훨씬 깔끔해집니다!
복수 결과	단일 값 반환	동적 배열로 복수 결과 반환 가능	하나의 함수로 여러 값을 가져올 수 있습니다!
성능	대규모 데이터에서 느려질 수 있음	효율적인 알고리즘, 정렬 데이터 이진 탐색 시 2~3배 빠름	체감상 확실히 빠릅니다!
검색 모드	제한적	위/아래, 이진 탐색 등 다양한 모드 제공	더 정교한 검색이 가능해요!

직접 해보는 XLOOKUP!

자, 제가 직접 써본 예시를 보여드릴게요. '제품ID'를 기준으로 '가격'을 가져오는 상황입니다.

// [예시 1] 제품ID (C2)를 찾아서 가격 (B열)을 가져오기
// 찾는 제품ID: C2
// 제품ID 목록: A열
// 가격 목록: B열
// 찾는 값이 없으면 "가격 미정" 표시
// 정확히 일치 (0), 첫 번째 값부터 검색 (1)
=XLOOKUP(C2, A:A, B:B, "가격 미정", 0, 1)

어떤가요? VLOOKUP보다 훨씬 직관적이고 군더더기가 없죠? 저는 이 한 줄로 '아, 진작 XLOOKUP으로 넘어올걸!' 후회했습니다.

엑셀이 혼자서 움직인다고? 동적 배열(Dynamic Arrays) 맛보기

XLOOKUP을 배우면서 또 한 번 저를 놀라게 한 것이 바로 '동적 배열'입니다. 이건 엑셀의 계산 방식을 근본적으로 바꿔놓았다고 해도 과언이 아닙니다. 비개발자 입장에서 설명하자면, "하나의 수식을 입력했는데, 결과가 혼자서 여러 셀에 자동으로 채워지는 마법!" 이라고나 할까요?

기존 엑셀에서는 하나의 셀에 하나의 수식을 넣으면 하나의 결과만 나왔죠. 하지만 동적 배열은 수식 하나가 여러 개의 결과(배열)를 반환하고, 엑셀이 이 결과들을 인접한 빈 셀에 자동으로 '넘쳐흐르듯이' 뿌려줍니다. 이걸 '스필(Spill)'이라고 부르더라고요.

이게 왜 대단하냐면요, 예전에는 특정 조건에 맞는 목록을 뽑거나 중복을 제거하고 정렬하려면 복잡한 배열 수식 ({ }로 묶는)을 쓰거나, 아니면 필터 기능을 사용한 후 복사-붙여넣기를 하거나, 아니면 '고급 필터' 같은 기능을 써야 했죠. 하지만 동적 배열 함수를 쓰면 단 하나의 수식으로 이 모든 걸 해결할 수 있습니다.

주요 동적 배열 함수 (제가 자주 쓰는 것들)

• FILTER: 특정 조건에 맞는 행만 추출합니다.
• UNIQUE: 중복된 값을 제거하고 고유한 값 목록을 만듭니다.
• SORT: 데이터를 정렬합니다.
• SORTBY: 여러 열을 기준으로 정렬합니다.
• SEQUENCE: 특정 순서로 숫자를 생성합니다 (예: 1, 2, 3...).

이 함수들을 사용하면 '헬퍼(Helper) 열'이라고 불리던 임시 계산 열들을 대폭 줄일 수 있습니다. 엑셀 파일이 훨씬 깔끔해지고, 불필요한 계산 부담이 줄어들기 때문에 파일 크기가 작아지고 로딩 시간도 단축되는 부수적인 효과까지 얻을 수 있습니다. 제가 직접 경험해보니, 복잡한 대시보드 파일을 열 때 확실히 부담이 덜하더라고요.

직접 해보는 동적 배열!

예를 들어, '전체 영업 실적' 데이터에서 '영업팀'의 실적만 뽑아내고 싶을 때, 예전에는 필터 기능을 쓰거나 IF 함수와 함께 복잡한 배열 수식을 써야 했죠. 이제 FILTER 함수 하나면 끝입니다.

// [예시 2] '원데이터' 시트의 A2:C100 범위에서
// B열이 "영업팀"인 데이터만 필터링하여 가져오기
=FILTER(원데이터!A2:C100, 원데이터!B2:B100="영업팀")

이 수식을 아무 빈 셀에 입력하면, 그 조건에 맞는 모든 '영업팀' 데이터가 자동으로 그 셀부터 아래로, 그리고 옆으로 채워집니다. 정말 마법 같지 않나요?

또 다른 예시로, 우리 회사에 존재하는 '고유한 제품 카테고리' 목록만 뽑고 싶을 때 UNIQUE 함수를 쓰면 됩니다.

// [예시 3] 제품 카테고리 (D열)에서 중복을 제거한 고유한 카테고리 목록 가져오기
=UNIQUE(D:D)

이 함수를 입력하면, D열에 있는 모든 카테고리 중 중복되지 않는 카테고리만 딱 한 번씩 나열됩니다. 예전에는 '데이터 > 중복된 항목 제거' 기능을 쓰거나, '피벗 테이블'을 만들어야 했는데, 이제는 함수 한 줄로 실시간으로 목록을 뽑아낼 수 있습니다.

비개발자가 헷갈리기 쉬운 XLOOKUP & 동적 배열 2-3가지 (+ 루카의 해결책)

제가 직접 사용하면서 헷갈렸던 부분들이 있었는데, 저처럼 비개발자라면 분명 겪을 법한 문제라고 생각해서 해결책과 함께 정리해봤습니다.

헷갈림 1: #SPILL! 오류, 공간을 비워라!

문제: 동적 배열 함수(FILTER, UNIQUE 등)를 썼는데 #SPILL! 에러가 뜨면서 결과가 나오지 않을 때가 있습니다. '이게 뭔데?' 싶어서 검색해보니...

설명 & 해결책: 이 오류는 동적 배열 함수가 여러 셀에 결과를 '스필(Spill)' 하려는데, 그 결과가 채워질 공간에 이미 다른 데이터나 수식이 있어서 채워지지 못할 때 발생합니다. 마치 물이 흘러내려야 하는데, 중간에 돌멩이가 막고 있는 것과 같죠.

해결책은 간단합니다. #SPILL! 오류가 뜬 셀부터, 예상되는 결과 범위까지의 셀들을 싹 다 비워주면 됩니다. 결과가 몇 줄이나 될지 모른다면, 일단 넉넉하게 비워두고 다시 수식을 입력해보세요. 그럼 마법처럼 결과가 '스필'되면서 나타날 겁니다. 저도 처음에는 이걸 모르고 '함수가 왜 안되지?' 하면서 몇 번을 다시 입력했는지 모릅니다.

헷갈림 2: XLOOKUP의 if_not_found 인수는 IFERROR와 뭐가 달라?

문제: XLOOKUP의 네 번째 인수인 [if_not_found]는 찾는 값이 없을 때 표시할 내용을 정해준다고 합니다. 그럼 기존에 IFERROR를 쓰던 것과 무슨 차이가 있을까요? 왠지 비슷해 보이는데...

설명 & 해결책: 기능적으로는 비슷해 보일 수 있습니다. 둘 다 에러 상황에 대한 처리를 하니까요. 하지만 XLOOKUP의 if_not_found는 '찾는 값이 없을 때'라는 특정 에러 상황에만 국한하여 처리하는 반면, IFERROR는 모든 종류의 에러 (예: #DIV/0!, #VALUE!, #REF!, #N/A 등)에 대해 일괄적으로 처리합니다.

가장 큰 차이점은 '함수의 간결성'입니다.

• IFERROR(VLOOKUP(...), "찾을 수 없음") : 두 함수를 중첩해서 써야 합니다.
• XLOOKUP(..., "찾을 수 없음") : XLOOKUP 함수 안에 이미 그 기능이 내장되어 있어서 훨씬 간결합니다.

루카의 팁: XLOOKUP을 쓸 때는 특별한 이유가 없다면 if_not_found 인수를 적극 활용해서 함수를 간결하게 만드세요. 코드든 엑셀 수식이든, 간결할수록 이해하기 쉽고 유지보수하기 좋습니다. 저는 주로 "" (빈 문자열)을 넣어서 셀을 깔끔하게 유지하는 편입니다.

헷갈림 3: 동적 배열과 절대 참조($)의 애매한 관계

문제: VLOOKUP 같은 함수를 쓸 때는 범위를 고정하기 위해 $ 기호를 붙여 절대 참조로 만드는 게 습관이었습니다. 그런데 동적 배열 함수를 쓸 때도 $를 써야 할까요? 아니면 쓰지 말아야 할까요?

설명 & 해결책: 결론부터 말하면, 경우에 따라 다릅니다. 동적 배열 함수는 한 번의 수식 입력으로 여러 셀에 결과를 '스필'합니다. 이때, 동적 배열 함수의 원천 범위 자체는 일반적으로 고정해두는 것이 좋습니다. 예를 들어, FILTER(A2:C100, ...)에서 A2:C100이 데이터 원본이라면, 다른 셀에서 이 함수를 복사해서 쓰더라도 원본 범위는 바뀌지 않게 $A$2:$C$100처럼 절대 참조로 만들어두는 것이 안전합니다.

하지만 만약 동적 배열의 결과가 또 다른 함수의 입력값으로 사용될 때, 그리고 그 함수를 아래로 복사해야 할 때는 상대 참조를 활용해야 할 수도 있습니다.

루카의 팁: 기본적으로 동적 배열 함수 내에서 원본 데이터를 가리키는 범위는 $A$1:$B$100처럼 절대 참조로 고정하는 것이 좋습니다. 하지만 만약 동적 배열 함수의 결과(예: A1# 처럼 스필된 범위)를 다른 함수에서 참조할 때, 그 A1#을 포함한 함수를 아래로 쭉 드래그해서 적용해야 한다면, 그 때는 절대 참조가 아닌 상대 참조 (A1#)를 사용해야 할 수도 있습니다. 중요한 건, 내가 무엇을 고정하고 싶고, 무엇을 움직이고 싶은지 명확히 생각하고 결정해야 한다는 겁니다. 저처럼 비개발자라면 일단 원본 데이터는 $, 결과는 필요에 따라 $를 붙일지 말지 고민해보는 습관을 들이는 게 좋다고 생각해요!

루카의 결론: VLOOKUP을 넘어선 엑셀의 신세계!

XLOOKUP과 동적 배열을 탐구하고 직접 사용해보면서, 저는 정말 '신세계'를 경험했습니다. 예전에는 엑셀 작업을 하면서 '이걸 이렇게밖에 못 하나?' 했던 비효율적인 부분들이 훨씬 간결하고 스마트하게 바뀌는 걸 보며 감탄 또 감탄했습니다. 단순히 함수 몇 개를 더 아는 것을 넘어, 엑셀이 데이터를 처리하는 방식 자체에 대한 이해를 한 단계 더 높일 수 있었던 경험이었습니다.

물론, VLOOKUP이 여전히 유용하게 쓰이는 경우도 있고, 모든 엑셀 사용자가 엑셀 365를 쓰는 건 아니기에 바로 XLOOKUP으로 넘어오지 못하는 분들도 계실 겁니다. 하지만 엑셀 365 환경에서 일하고 있다면, XLOOKUP과 동적 배열은 선택이 아닌 필수라고 감히 말씀드리고 싶습니다. 복잡했던 데이터 작업이 훨씬 단순해지고, 실수가 줄어들며, 무엇보다 '엑셀, 이젠 좀 더 스마트하게 쓸 수 있겠는데?' 하는 자신감을 얻게 될 거예요!

저처럼 코딩은 모르지만, 효율적인 데이터 관리에 목마른 모든 비개발자분들께 이 강력한 엑셀 기능들을 꼭 한번 사용해보시라고 권해드립니다. 제가 직접 부딪히고 해결하며 느낀 점들이 여러분의 엑셀 생활에 조금이나마 도움이 되었기를 바랍니다!

핵심 요약 (3줄)

• XLOOKUP은 VLOOKUP의 모든 단점(방향, 열 번호, 에러 처리)을 극복한 차세대 조회 함수입니다.
• 동적 배열은 하나의 수식으로 여러 결과를 자동으로 '스필'하여, 복잡한 데이터 정렬/필터링/추출 작업을 압도적으로 간결하게 만듭니다.
• 비개발자도 충분히 배우고 활용할 수 있으며, 엑셀 작업의 효율성과 생산성을 혁신적으로 높여줄 핵심 기능입니다.

자주 묻는 질문 (FAQ)

Q1: XLOOKUP은 모든 엑셀 버전에서 되나요? A1: 아쉽게도 그렇지 않습니다. XLOOKUP은 Microsoft 365 구독 버전에만 포함된 함수입니다. 엑셀 2019나 2016과 같은 영구 라이선스 버전에서는 사용할 수 없습니다. 이 때문에 아직 많은 기업이나 개인이 VLOOKUP이나 INDEX/MATCH를 사용하고 있습니다.

Q2: 동적 배열 함수는 VLOOKUP 대신 완전히 쓸 수 있나요? A2: 동적 배열 함수는 VLOOKUP의 기능을 대체하는 것이 아니라, 데이터를 처리하고 배열을 반환하는 방식이 다른 별개의 강력한 기능입니다. XLOOKUP이 VLOOKUP의 직접적인 대체제라면, FILTER, UNIQUE, SORT 같은 동적 배열 함수들은 기존에 여러 단계의 수동 작업이나 복잡한 배열 수식으로 처리했던 작업을 단일 함수로 자동화해주는 역할을 합니다. 물론, XLOOKUP 자체도 동적 배열을 반환할 수 있어 여러 열의 결과를 한 번에 가져오는 등 시너지를 낼 수 있습니다.

Q3: XLOOKUP과 INDEX/MATCH 중 뭐가 더 좋나요? (비개발자 시선) A3: 엑셀 365 사용자라면 XLOOKUP이 압도적으로 좋습니다. 기존에는 VLOOKUP의 한계(왼쪽 검색 불가, 열 추가 시 오류) 때문에 INDEX/MATCH 조합이 '더 전문가스러운' 대안으로 여겨졌습니다. 하지만 INDEX/MATCH는 두 개의 함수를 중첩해야 하고, 특히 비개발자 입장에서는 직관성이 떨어져 이해하기 어려운 경우가 많습니다. XLOOKUP은 INDEX/MATCH의 모든 장점을 가져오면서도 단 하나의 함수로 구성되어 훨씬 직관적이고 사용하기 쉽습니다. 저는 이제 INDEX/MATCH 대신 XLOOKUP만 사용합니다!

---

오늘의 '루카의 탐구생활'은 여기까지입니다. 이 글을 읽으시는 비개발자 여러분도 XLOOKUP과 동적 배열을 두려워하지 말고, 꼭 한번 직접 써보시길 바랍니다. 저처럼 비개발자도 충분히 이해하고 활용해서 엑셀 고수가 될 수 있습니다! 다음에도 궁금한 IT 이야기가 있다면 직접 파보고 또 찾아오겠습니다. 감사합니다!