수천 개의 숫자와 텍스트가 담긴 Excel 통합 문서에는 같은 값이 여러 번 반복되어 있기 마련입니다. 때로는 이렇게 중복되거나 일치하는 값을 찾아야 할 필요가 생기죠. 이번 글에서는 Excel 365에서 일치하는 값을 찾는 여러 가지 방법을 단계별로 살펴보겠습니다.
두 개의 서로 다른 워크시트와 두 개의 열에서 동일한 단어나 숫자를 찾는 방법을 다룰 예정이며, EXACT, MATCH, VLOOKUP 함수를 활용합니다. 일부 방법은 Excel 웹 버전에서는 작동하지 않을 수 있지만, 데스크톱 버전에서는 모두 정상적으로 사용할 수 있습니다.
Excel 함수란 무엇인가?
이미 함수를 사용해 본 경험이 있다면 이 섹션은 건너뛰어도 좋습니다.
Excel 함수는 하나의 작업을 수행하기 위해 일련의 단계를 자동으로 처리하는 미니 앱과 같은 것입니다. 가장 많이 쓰이는 함수들은 수식 탭에서 성격별로 분류되어 제공됩니다.
- 자동 합계(AutoSum)
- 최근 사용한 함수(Recently Used)
- 재무(Financial)
- 논리(Logical)
- 텍스트(Text)
- 날짜/시간(Date & Time)
- 찾기/참조(Lookup & Reference)
- 수학/삼각(Math & Trig)
- 추가 함수(More Functions)
추가 함수 카테고리 안에는 통계, 공학, 큐브, 정보, 호환성, 웹 하위 분류가 포함되어 있습니다.
EXACT 함수로 두 열 비교하기
EXACT 함수는 두 열의 행을 하나씩 훑으며 셀 값이 일치하는지 확인하는 역할을 합니다. 이름 그대로 '완전히 동일한' 값만 인정하며, 대소문자를 구분합니다. 따라서 New York과 new york은 서로 다른 값으로 판정됩니다.
아래 예시에는 Tickets(티켓)와 Receipts(영수증) 두 개의 텍스트 열이 있습니다. 데이터가 10개 정도라면 눈으로 직접 비교할 수 있지만, 1,000행 이상이라면 이야기가 달라집니다. 바로 그럴 때 EXACT 함수가 빛을 발합니다.
C2 셀에 커서를 놓고 수식 입력줄에 다음 수식을 입력합니다.
=EXACT(E2:E10,F2:F10)
E2:E10은 첫 번째 값 열, F2:F10은 바로 옆의 열을 가리킵니다. Enter 키를 누르면 Excel이 각 행의 두 값을 비교해 일치하면 TRUE, 일치하지 않으면 FALSE를 반환합니다. 셀 하나가 아니라 범위를 지정했기 때문에 수식이 아래 셀로 자동 확장(spill)되어 나머지 모든 행까지 한 번에 평가됩니다.
다만 이 방식에는 제약이 있습니다. 같은 행에 놓인 두 셀만 비교할 수 있어서, 예를 들어 A2와 B3처럼 서로 다른 행의 셀은 비교할 수 없습니다. 그런 경우에는 MATCH 함수를 활용하면 됩니다.
MATCH 함수로 값의 위치 찾기
MATCH 함수는 특정 값이 셀 범위 안에서 어느 위치에 있는지 알려줍니다.
예를 들어 아래 재고 데이터에서 특정 SKU(재고 관리 코드)가 몇 번째 행에 있는지 찾고 싶다고 가정해 보겠습니다.
AA003이 몇 번째 행에 있는지 확인하려면 다음 수식을 사용합니다.
=MATCH(J1,E2:E9,0)
J1은 찾으려는 값이 입력된 셀, E2:E9는 검색 대상 범위입니다. 수식 끝의 0은 Excel에게 정확히 일치하는 값만 찾도록 지시하는 옵션입니다. 숫자를 검색할 때는 1을 넣어 조회값보다 작은 최근접 값을, 2를 넣어 큰 최근접 값을 찾을 수도 있습니다.
그렇다면 AA003의 가격까지 함께 알고 싶다면 어떻게 할까요? 이럴 때 VLOOKUP 함수가 필요합니다.
VLOOKUP 함수로 연관 값 불러오기
VLOOKUP의 V는 세로(Vetical)를 의미합니다. 즉, 열 안에서 주어진 값을 검색할 수 있을 뿐 아니라, 찾은 값과 같은 행에 있는 다른 열의 값을 반환할 수도 있습니다.
참고로 최신 Microsoft 365 버전을 사용하고 있다면 VLOOKUP보다 더 유연한 XLOOKUP 함수도 활용할 수 있습니다.
같은 재고 데이터를 활용해 가격을 찾아보겠습니다.
아래 수식을 입력합니다.
=VLOOKUP(J1,E2:G9,3,FALSE)
J1은 일치시키려는 값이 있는 셀입니다. E2:G9는 작업 범위이며, VLOOKUP은 이 범위의 첫 번째 열에서만 일치 여부를 확인합니다. 숫자 3은 범위의 시작점부터 세 번째 열에 있는 값을 가져오라는 의미입니다.
따라서 J1에 SKU를 입력하면 VLOOKUP이 일치하는 항목을 찾아 오른쪽으로 세 번째 열의 셀 값을 반환합니다. 마지막의 FALSE는 정확히 일치하는 값만 찾으라는 뜻이며, TRUE로 설정하면 근사치 일치를 허용합니다.
서로 다른 두 시트에서 일치하는 값 찾기
앞서 소개한 함수들은 모두 서로 다른 두 시트에 걸쳐 사용할 수 있습니다. 여기서는 EXACT 함수를 예로 들어 설명하겠지만, 이 방식은 거의 모든 함수에 응용할 수 있습니다. 물론 시트나 통합 문서 간 셀을 연결하는 다른 방법들도 있습니다.
Holders 시트에서 작업한다고 가정하고 다음 수식을 입력합니다.
=EXACT(D2:D10,Tickets!E2:E10)
D2:D10은 Holders 시트에서 선택한 범위입니다. 쉼표를 입력한 후 Tickets 시트 탭을 클릭하고 두 번째 범위를 드래그해 선택하면 됩니다.
Tickets!E2:E10처럼 시트 이름과 범위가 함께 참조되는 것을 확인할 수 있습니다. 이 예시에서는 각 행이 모두 일치하므로 결과가 전부 TRUE로 표시됩니다.
이 함수들을 더 다양하게 활용하는 방법
일치하는 값을 찾고 검색하는 함수들을 익혀두면 실무에서 활용 범위가 크게 넓어집니다. 특히 INDEX 함수와 MATCH 함수를 조합하면 VLOOKUP과 유사한 기능을 더 유연하게 구현할 수 있으니 함께 익혀두는 것을 추천합니다.
Excel 함수로 일치하는 값을 찾는 자신만의 꿀팁이 있거나, 추가로 궁금한 점이 있다면 댓글로 남겨주세요.