찾기 함수와 동적 배열로 데이터 연결하기

흩어진 표를 연결해야 진짜 일이 풀린다

실무 엑셀 파일을 열어 보면 데이터가 한곳에만 얌전히 모여 있는 경우는 많지 않습니다. 주문 내역 시트에는 상품 코드만 있고, 상품명과 단가는 다른 시트의 상품 기준표에 들어 있습니다. 직원별 실적표에는 사원번호만 있고, 부서명과 직급은 인사 기준표에 따로 있습니다. 고객 문의 목록에는 고객번호만 있고, 고객명과 연락처는 고객 관리표에 따로 있을 수도 있습니다.

이때 필요한 것이 바로 찾기 함수입니다. 찾기 함수는 엑셀 안에서 "이 값과 같은 것을 기준표에서 찾아서, 옆에 있는 필요한 정보를 가져와 줘"라고 요청하는 도구입니다. 사람이 눈으로 상품 코드를 하나씩 대조하면 오래 걸리고 실수도 생깁니다. 하지만 찾기 함수를 사용하면 원본 데이터와 기준표를 자동으로 연결할 수 있습니다.

예를 들어 주문표가 다음과 같다고 해 보겠습니다.

그리고 상품표가 따로 있습니다.

주문표에 상품명과 단가를 직접 타이핑해 넣을 수도 있습니다. 하지만 상품명이 바뀌거나 단가가 수정되면 모든 주문 행을 다시 고쳐야 합니다. 더 좋은 방법은 상품코드를 기준으로 상품표에서 상품명과 단가를 가져오는 것입니다. 이렇게 하면 기준표만 정확히 관리해도 여러 표가 함께 정리됩니다.

XLOOKUP으로 원하는 값을 바로 가져오기

최신 엑셀에서 가장 먼저 익혀야 할 찾기 함수는 XLOOKUP입니다. XLOOKUP은 기준값을 찾고, 그 기준값과 같은 위치에 있는 다른 범위의 값을 반환합니다. 말은 조금 길지만 구조는 단순합니다.

=XLOOKUP(찾을값, 찾을범위, 반환범위, 못찾을때)

주문표의 상품코드가 B2에 있고, 상품표의 상품코드 범위가 F:F, 상품명 범위가 G:G라면 다음과 같이 쓸 수 있습니다.

=XLOOKUP(B2, F:F, G:G, "코드 확인")

이 수식은 이렇게 읽으면 됩니다. "B2에 있는 상품코드를 F열에서 찾아라. 찾았다면 같은 행의 G열 값을 가져와라. 만약 못 찾으면 '코드 확인'이라고 표시하라."

같은 방식으로 단가도 가져올 수 있습니다.

=XLOOKUP(B2, F:F, H:H, "코드 확인")

여기서는 F열에서 상품코드를 찾고, H열의 단가를 반환합니다. 찾기 함수에서 가장 중요한 감각은 "찾을 범위"와 "반환할 범위"가 서로 나란히 대응된다는 점입니다. 찾을 범위의 첫 번째 값과 반환 범위의 첫 번째 값, 찾을 범위의 두 번째 값과 반환 범위의 두 번째 값이 서로 짝을 이룹니다.

엑셀 표 기능을 사용하고 있다면 수식은 더 읽기 쉬워집니다. 상품 기준표의 이름이 상품표이고 열 이름이 상품코드, 상품명, 단가라면 다음처럼 작성할 수 있습니다.

=XLOOKUP([@상품코드], 상품표[상품코드], 상품표[상품명], "코드 확인")

이 수식은 처음에는 낯설어 보일 수 있지만, 사실 훨씬 자연스럽습니다. 현재 행의 상품코드로 상품표의 상품코드 열을 검색하고, 상품표의 상품명 열에서 결과를 가져온다는 뜻이 그대로 드러납니다.

XLOOKUP의 장점은 기본적으로 정확히 일치하는 값을 찾는다는 점입니다. 실무에서 상품코드, 고객번호, 사원번호처럼 고유한 값을 찾을 때는 대부분 정확히 같은 값을 찾아야 합니다. P1001과 비슷한 값을 찾는 것이 아니라 정확히 P1001을 찾아야 하는 것입니다.

또 하나의 장점은 왼쪽과 오른쪽을 가리지 않는다는 것입니다. 찾을 값이 오른쪽 열에 있고 가져올 값이 왼쪽 열에 있어도 문제없습니다. 예를 들어 상품명으로 상품코드를 찾아야 한다면 다음과 같이 쓸 수 있습니다.

=XLOOKUP(E2, 상품표[상품명], 상품표[상품코드], "상품명 확인")

예전 방식에 익숙한 사람에게는 이 점이 아주 크게 느껴집니다. 표의 구조를 억지로 바꾸지 않아도 되기 때문입니다.

못 찾는 값까지 설계해야 좋은 수식이다

찾기 함수는 값을 잘 찾는 것도 중요하지만, 값을 못 찾았을 때 어떻게 보여 줄지도 중요합니다. 실무 파일에서 가장 당황스러운 순간은 수식이 #N/A 오류를 뿜어내는 순간입니다. #N/A는 대체로 "찾는 값이 없다"는 뜻입니다.

예를 들어 주문표에 P9999라는 상품코드가 들어 있는데 상품표에는 이 코드가 없다면 XLOOKUP은 값을 찾을 수 없습니다. 이때 네 번째 인수인 못찾을때를 넣어 두면 오류 대신 사람이 이해할 수 있는 메시지를 보여 줄 수 있습니다.

=XLOOKUP(B2, 상품표[상품코드], 상품표[상품명], "상품코드 미등록")

이렇게 하면 단순히 오류를 숨기는 것이 아니라, 데이터에 어떤 문제가 있는지 알려 주는 안내판을 세우는 셈입니다. 좋은 엑셀 파일은 계산만 잘하는 파일이 아닙니다. 문제가 생겼을 때 어디를 확인해야 하는지도 알려 주는 파일입니다.

다만 주의할 점이 있습니다. 못찾을때에 무조건 빈칸 ""을 넣으면 화면은 깔끔해지지만, 문제를 놓치기 쉽습니다. 보고서의 최종 화면에서는 빈칸이 필요할 수 있지만, 데이터를 검토하는 단계에서는 "코드 확인", "미등록", "기준표 확인"처럼 원인을 드러내는 문구가 더 좋습니다.

VLOOKUP은 아직도 자주 만난다

XLOOKUP이 편리하다고 해서 VLOOKUP을 몰라도 되는 것은 아닙니다. 오래된 회사 파일, 거래처가 보내온 양식, 이전 담당자가 만든 문서에는 여전히 VLOOKUP이 많이 들어 있습니다. 따라서 새로 만들 때는 XLOOKUP을 우선 사용하더라도, 기존 파일을 읽고 고치려면 VLOOKUP의 구조를 알아야 합니다.

VLOOKUP의 기본 구조는 다음과 같습니다.

=VLOOKUP(찾을값, 표범위, 열번호, 정확히일치여부)

예를 들어 B2의 상품코드를 F:H 범위에서 찾고, 그 범위의 두 번째 열인 상품명을 가져오려면 다음과 같이 씁니다.

=VLOOKUP(B2, F:H, 2, FALSE)

단가를 가져오려면 세 번째 열을 반환해야 하므로 열 번호를 3으로 바꿉니다.

=VLOOKUP(B2, F:H, 3, FALSE)

여기서 마지막의 FALSE는 정확히 일치하는 값을 찾겠다는 뜻입니다. 실무에서는 이 값을 생략하지 않는 습관이 매우 중요합니다. VLOOKUP의 마지막 인수를 생략하거나 TRUE로 두면 비슷한 값을 찾는 방식으로 동작할 수 있습니다. 이 방식은 구간별 수수료율이나 등급표처럼 특수한 상황에서는 유용하지만, 상품코드나 사원번호를 찾을 때는 엉뚱한 결과를 만들 수 있습니다.

VLOOKUP을 사용할 때 특히 조심해야 할 점은 세 가지입니다.

• 찾을 값은 표범위의 첫 번째 열에 있어야 합니다.

• 가져올 열은 숫자로 지정하므로, 중간에 열을 삽입하거나 삭제하면 수식 결과가 틀어질 수 있습니다.

• 정확히 일치 검색이 필요하면 마지막 인수에 반드시 FALSE 또는 0을 넣어야 합니다.

예를 들어 =VLOOKUP(B2, F:H, 3, FALSE)라는 수식에서 F:H 범위의 가운데에 새 열이 추가되면 원래 세 번째 열이던 단가가 네 번째 열로 밀릴 수 있습니다. 그런데 수식의 열 번호는 여전히 3입니다. 수식은 멀쩡해 보이지만 다른 값을 가져올 수 있습니다. 그래서 VLOOKUP 파일을 수정할 때는 열 삽입과 삭제에 특히 신중해야 합니다.

INDEX와 MATCH는 찾기 함수의 조립식 도구다

XLOOKUP이 등장하기 전에는 INDEX와 MATCH를 결합한 방식이 매우 강력한 조회 방법으로 사용되었습니다. 지금도 복잡한 파일에서는 이 조합을 자주 만납니다. 두 함수의 역할을 나누어 보면 어렵지 않습니다.

MATCH는 위치를 찾습니다. 예를 들어 어떤 상품코드가 상품코드 목록에서 몇 번째에 있는지 알려 줍니다.

=MATCH(B2, 상품표[상품코드], 0)

마지막의 0은 정확히 일치하는 값을 찾겠다는 뜻입니다. 만약 B2의 상품코드가 상품표의 세 번째 행에 있다면 이 수식은 3을 반환합니다.

INDEX는 지정한 위치의 값을 가져옵니다.

=INDEX(상품표[상품명], 3)

이 수식은 상품명 열에서 세 번째 값을 가져옵니다. 이제 두 함수를 합치면 이런 구조가 됩니다.

=INDEX(상품표[상품명], MATCH(B2, 상품표[상품코드], 0))

읽는 순서는 안쪽부터입니다. 먼저 MATCH가 B2의 상품코드가 상품표에서 몇 번째에 있는지 찾습니다. 그다음 INDEX가 상품명 열에서 그 번째에 있는 값을 가져옵니다.

이 방식의 장점은 반환할 범위를 자유롭게 지정할 수 있다는 점입니다. 찾을 범위가 오른쪽에 있고 반환할 범위가 왼쪽에 있어도 문제없습니다. 즉, VLOOKUP처럼 "첫 번째 열에서만 찾아야 한다"는 제한을 받지 않습니다.

다중 조건 조회는 조건을 하나의 열쇠로 만든다

실무에서는 조건이 하나만 있는 조회보다 조건이 여러 개인 조회가 자주 등장합니다. 예를 들어 상품별 단가가 지역에 따라 다르다고 해 보겠습니다.

이때 상품코드가 P1001이면서 지역이 부산인 행의 단가를 찾아야 합니다. 조건이 두 개이므로 단순히 상품코드만 찾으면 안 됩니다. P1001은 서울에도 있고 부산에도 있기 때문입니다.

동적 배열을 지원하는 엑셀에서는 다음과 같은 방식으로 여러 조건을 동시에 비교할 수 있습니다.

=INDEX(단가범위, MATCH(1, (상품코드범위=H2)*(지역범위=I2), 0))

처음 보면 수식이 갑자기 어려워 보입니다. 하지만 원리는 간단합니다. (상품코드범위=H2)는 상품코드가 맞는 행을 TRUE 또는 FALSE로 표시합니다. (지역범위=I2)도 지역이 맞는 행을 TRUE 또는 FALSE로 표시합니다. 두 결과를 곱하면 둘 다 맞는 행만 1이 됩니다. 하나라도 틀리면 0이 됩니다.

그다음 MATCH(1, ..., 0)은 1이 처음 나오는 위치를 찾고, INDEX는 그 위치의 단가를 가져옵니다. 조건 두 개를 하나의 열쇠처럼 만들어서 조회하는 방식입니다.

같은 원리를 XLOOKUP으로도 표현할 수 있습니다.

=XLOOKUP(1, (상품코드범위=H2)*(지역범위=I2), 단가범위, "조건 확인")

이 수식은 "조건을 모두 만족하는 행을 1로 표시한 뒤, 그 1을 찾아 단가를 가져오라"는 뜻입니다. 다중 조건 조회를 이해하면 엑셀은 단순한 계산 도구에서 데이터 연결 도구로 한 단계 올라갑니다.

동적 배열은 수식 하나가 결과표를 만든다

지금까지의 찾기 함수는 대체로 한 셀에 하나의 결과를 가져오는 방식이었습니다. 하지만 최신 엑셀의 동적 배열 함수는 수식 하나로 여러 셀에 결과를 자동으로 펼쳐 줍니다. 이것을 스필이라고 합니다. 영어로는 spill, 즉 물이 흘러넘치듯 결과가 주변 셀로 확장된다는 뜻입니다.

예를 들어 판매표에서 특정 지역의 주문만 따로 보고 싶다고 해 보겠습니다. 예전에는 필터를 직접 걸거나, 조건을 만족하는 행을 복사해서 다른 곳에 붙여 넣어야 했습니다. 하지만 FILTER 함수를 사용하면 조건에 맞는 행 목록이 자동으로 만들어집니다.

=FILTER(판매표, 판매표[지역]=H2, "결과 없음")

이 수식은 판매표에서 지역이 H2와 같은 행만 골라 보여 줍니다. H2에 "서울"을 입력하면 서울 주문만 나오고, "부산"으로 바꾸면 부산 주문만 자동으로 바뀝니다. 보고서의 조건 선택 셀과 연결하면 작은 조회 화면을 만들 수 있습니다.

조건을 여러 개 넣을 수도 있습니다. 예를 들어 지역이 H2와 같고, 매출이 I2 이상인 행만 보고 싶다면 다음처럼 씁니다.

=FILTER(판매표, (판매표[지역]=H2)*(판매표[매출]>=I2), "결과 없음")

여기서도 곱하기는 "그리고"의 의미로 생각하면 됩니다. 지역 조건도 맞고 매출 조건도 맞는 행만 남습니다.

UNIQUE와 SORT로 목록을 자동 정리하기

보고서를 만들다 보면 중복 없는 목록이 필요할 때가 많습니다. 판매 데이터에는 같은 고객명이 여러 번 등장할 수 있습니다. 하지만 고객별 요약표를 만들려면 고객명 목록은 한 번씩만 있으면 됩니다. 이때 UNIQUE 함수를 사용합니다.

=UNIQUE(판매표[고객명])

이 수식 하나만 입력하면 고객명 열에서 중복을 제거한 목록이 자동으로 만들어집니다. 원본 판매표에 새 고객이 추가되면 목록도 함께 확장됩니다. 직접 복사해서 "중복된 항목 제거"를 실행하는 방식보다 훨씬 안정적입니다.

목록을 정렬하고 싶다면 SORT를 함께 사용합니다.

=SORT(UNIQUE(판매표[고객명]))

이 수식은 고객명에서 중복을 제거한 뒤 가나다순으로 정렬합니다. 함수가 안쪽부터 계산된다는 원리를 떠올리면 이해하기 쉽습니다. 먼저 UNIQUE가 고유 목록을 만들고, 그 결과를 SORT가 정렬합니다.

조건에 맞는 목록을 뽑고, 중복을 제거하고, 정렬까지 한 번에 처리할 수도 있습니다.

=SORT(UNIQUE(FILTER(판매표[고객명], 판매표[지역]=H2, "결과 없음")))

이 수식은 H2에 입력한 지역의 고객명만 골라낸 뒤, 중복을 제거하고, 정렬합니다. 처음 보면 수식이 길어 보이지만, 실제로는 세 가지 작업을 차례로 묶은 것입니다. "거르기, 중복 제거하기, 정렬하기"라는 흐름으로 읽으면 됩니다.

동적 배열 함수의 매력은 원본 데이터가 바뀌면 결과도 자동으로 바뀐다는 데 있습니다. 어제까지 없던 고객이 오늘 판매표에 추가되면 고객 목록에도 자동으로 나타납니다. 실무에서 이런 자동화는 작은 편리함이 아니라 큰 시간 절약입니다.

스필 범위와 # 기호 이해하기

동적 배열 수식이 여러 셀로 결과를 펼치면, 그 전체 결과 범위를 스필 범위라고 부릅니다. 예를 들어 K2 셀에 =UNIQUE(판매표[고객명])을 입력했고 결과가 K2:K15까지 펼쳐졌다면 K2:K15가 스필 범위입니다.

이 스필 범위는 # 기호로 참조할 수 있습니다.

=COUNTA(K2#)

이 수식은 K2에서 시작해 펼쳐진 전체 결과의 개수를 셉니다. 중요한 점은 결과가 나중에 K20까지 늘어나도 수식을 고칠 필요가 없다는 것입니다. K2#는 현재 펼쳐진 전체 범위를 자동으로 가리킵니다.

차트나 다른 계산식과 연결할 때도 이 개념이 유용합니다. 동적 배열의 결과를 기반으로 합계, 개수, 평균을 구하면 원본 데이터 변화에 따라 보고서가 자연스럽게 따라 움직입니다.

#SPILL! 오류는 자리가 부족하다는 신호다

동적 배열을 처음 사용할 때 가장 자주 만나는 오류가 #SPILL!입니다. 이 오류는 대체로 수식 결과가 여러 셀로 펼쳐져야 하는데, 펼쳐질 공간에 문제가 있다는 뜻입니다. 쉽게 말해 "결과가 나가야 할 자리에 뭔가 막고 있습니다"라는 신호입니다.

가장 흔한 원인은 결과가 펼쳐질 범위 안에 이미 값이 들어 있는 경우입니다. 예를 들어 K2에 고객 목록을 만들려고 했는데 K5에 메모가 적혀 있다면, 엑셀은 K2부터 아래로 결과를 펼칠 수 없습니다. 이때 #SPILL!이 나타납니다. 해결 방법은 간단합니다. 막고 있는 값을 지우거나, 수식을 더 넓은 빈 공간으로 옮기면 됩니다.

병합된 셀도 문제를 일으킬 수 있습니다. 동적 배열은 여러 셀에 결과를 자연스럽게 펼쳐야 하는데, 중간에 병합된 셀이 있으면 정상적으로 확장하기 어렵습니다. 이 경우 병합을 해제하거나 결과가 나올 위치를 바꾸는 것이 좋습니다.

엑셀 표 내부에 동적 배열 수식을 넣는 것도 주의해야 합니다. 엑셀 표는 행 단위로 구조화된 계산을 잘 처리하지만, 표 안의 한 셀에서 여러 셀로 결과가 흘러나가는 방식과는 맞지 않을 수 있습니다. 동적 배열 결과표는 보통 원본 표 바깥의 빈 영역에 만드는 것이 안전합니다.

#SPILL! 오류가 나면 당황하지 말고 다음 순서로 확인해 보세요.

• 결과가 펼쳐질 위치에 값, 공백 문자, 숨은 데이터가 있는지 확인합니다.

• 결과 범위에 병합된 셀이 있는지 확인합니다.

• 동적 배열 수식을 엑셀 표 안이 아니라 표 밖의 빈 공간에 입력했는지 확인합니다.

• 수식이 지나치게 넓은 범위를 반환하도록 작성되어 있지 않은지 확인합니다.

여기서 중요한 태도는 오류를 실패로 보지 않는 것입니다. #SPILL!은 수식이 틀렸다는 뜻이라기보다, 결과가 펼쳐질 길이 막혔다는 안내에 가깝습니다. 길만 터 주면 수식은 다시 움직입니다.

찾기 함수와 동적 배열을 함께 쓰는 실무 흐름

이제 찾기 함수와 동적 배열을 하나의 흐름으로 연결해 보겠습니다. 판매 원본에는 주문번호, 날짜, 상품코드, 고객번호, 수량만 있다고 가정하겠습니다. 상품명과 단가는 상품표에서 가져와야 하고, 고객명은 고객표에서 가져와야 합니다.

먼저 판매표에 상품명을 가져옵니다.

=XLOOKUP([@상품코드], 상품표[상품코드], 상품표[상품명], "상품 확인")

다음으로 단가를 가져옵니다.

=XLOOKUP([@상품코드], 상품표[상품코드], 상품표[단가], "상품 확인")

고객명도 같은 방식으로 연결합니다.

=XLOOKUP([@고객번호], 고객표[고객번호], 고객표[고객명], "고객 확인")

이렇게 원본 판매표가 풍부해지면 그다음 분석이 쉬워집니다. 특정 고객의 주문만 보고 싶다면 FILTER를 사용할 수 있습니다.

=FILTER(판매표, 판매표[고객명]=H2, "주문 없음")

고객 목록을 자동으로 만들고 싶다면 다음처럼 작성합니다.

=SORT(UNIQUE(판매표[고객명]))

상품별 목록이 필요하다면 상품명 열을 기준으로 고유 목록을 만들 수 있습니다.

=SORT(UNIQUE(판매표[상품명]))

이 흐름이 실무 엑셀의 핵심입니다. 기준표는 따로 정확히 관리하고, 원본표는 코드 중심으로 쌓고, 찾기 함수로 필요한 정보를 연결하고, 동적 배열 함수로 조건에 맞는 목록을 자동 생성합니다. 이렇게 만들면 파일은 단순한 입력장이 아니라 스스로 갱신되는 업무 시스템에 가까워집니다.

실습: 주문표를 자동으로 완성하기

이번 장의 내용을 손에 익히려면 작은 주문표를 직접 만들어 보는 것이 좋습니다. 먼저 상품표를 만들고 상품코드, 상품명, 단가 열을 준비합니다. 그다음 주문표를 만들고 주문번호, 상품코드, 수량을 입력합니다. 주문표에는 상품명, 단가, 금액 열을 추가합니다.

상품명 열에는 다음 수식을 넣습니다.

=XLOOKUP([@상품코드], 상품표[상품코드], 상품표[상품명], "상품코드 확인")

단가 열에는 다음 수식을 넣습니다.

=XLOOKUP([@상품코드], 상품표[상품코드], 상품표[단가], "상품코드 확인")

금액 열에는 수량과 단가를 곱합니다.

=[@수량]*[@단가]

이제 주문표에 상품코드를 입력하기만 해도 상품명과 단가가 자동으로 채워지고 금액까지 계산됩니다. 만약 없는 상품코드를 입력하면 "상품코드 확인"이라는 메시지가 나타납니다. 이 메시지는 단순한 오류가 아니라 기준표를 보완하라는 신호입니다.

마지막으로 주문표 바깥에 상품명 목록을 자동으로 만들어 봅니다.

=SORT(UNIQUE(주문표[상품명]))

그리고 특정 상품의 주문만 보고 싶다면 조건 셀에 상품명을 입력하고 다음 수식을 사용합니다.

=FILTER(주문표, 주문표[상품명]=H2, "주문 없음")

이 정도만 제대로 익혀도 실무에서 반복하던 복사, 붙여넣기, 눈대중 확인 작업이 크게 줄어듭니다.

이번 장에서 꼭 가져갈 감각

찾기 함수는 단순히 값을 가져오는 함수가 아닙니다. 여러 표 사이에 다리를 놓는 함수입니다. 상품코드와 상품명, 사원번호와 부서명, 고객번호와 고객명처럼 서로 떨어져 있는 정보를 연결해 업무 데이터의 의미를 완성합니다.

XLOOKUP은 새 파일을 만들 때 가장 추천할 만한 조회 함수입니다. 읽기 쉽고, 왼쪽 조회도 가능하며, 못 찾았을 때의 메시지도 자연스럽게 처리할 수 있습니다. VLOOKUP은 기존 파일에서 여전히 자주 만나므로 구조와 한계를 이해해야 합니다. 특히 열 번호와 정확히 일치 옵션은 반드시 조심해야 합니다.

INDEX와 MATCH는 조회의 원리를 더 깊게 이해하게 해 줍니다. 위치를 찾고, 그 위치의 값을 가져오는 방식은 복잡한 조건 조회를 이해하는 데 큰 도움이 됩니다. 여러 조건을 동시에 비교해 하나의 열쇠로 만드는 사고방식도 여기서 배울 수 있습니다.

마지막으로 FILTER, SORT, UNIQUE 같은 동적 배열 함수는 결과를 한 셀에 가두지 않습니다. 조건에 맞는 목록을 자동으로 펼치고, 중복을 제거하고, 정렬합니다. 다만 결과가 펼쳐질 공간이 필요하므로 #SPILL! 오류가 나면 주변 셀과 병합 여부를 먼저 확인해야 합니다.

다음 장에서는 이렇게 연결된 데이터를 더 깔끔하게 정리하는 방법으로 넘어갑니다. 정렬, 필터, 유효성 검사를 익히면 데이터 입력 단계에서부터 실수를 줄이고, 필요한 정보만 빠르게 골라 보는 힘이 생깁니다.