함수·수식을 말로 만들기 — VLOOKUP·INDEX/MATCH·IF

함수 이름 대신 상황을 말한다

1장에서 엑셀은 이제 "말로 시킨다"고 했습니다. 그 첫 실전이 수식입니다. 사람들이 가장 자주 막히고, AI가 가장 잘 메워 주는 지점이기도 합니다.

요령은 하나, 상황을 그대로 설명하기

함수 이름을 떠올리려 애쓰지 마세요. 무엇이 어디에 있고, 무엇을 얻고 싶은지만 말하면 됩니다. 아래 한마디면 ChatGPT가 곧바로 수식을 만들어 줍니다.

B열 상품코드로 '단가표' 시트에서 단가를 찾아 C열에 넣고 싶어. 엑셀 수식으로 알려주고, 어디에 붙여넣는지도 말해줘.

돌아오는 답은 =VLOOKUP(B2, 단가표!$A$2:$B$500, 2, FALSE) 한 줄과, "C2에 넣고 아래로 끌어내리세요"라는 안내입니다. 인수 순서도, 마지막 FALSE의 의미도 몰라도 됩니다.

열 이름을 붙이면 정확도가 오른다

같은 요청이라도 "B열", "C열"처럼 실제 위치를 붙이면 결과가 훨씬 정확해집니다. AI가 상상으로 열을 배치하지 않게 만드는 겁니다. 표 첫 줄 몇 개를 그대로 붙여 넣어 주면 더 좋습니다.

결과 모양까지 말하면 다시 고칠 일이 준다

"단가를 넣어줘"보다 "단가를 C열에 숫자로 넣고, 없는 코드는 빈칸으로 둬"가 낫습니다. 예외 처리를 미리 말해 두면 #N/A를 나중에 손볼 일이 사라집니다.

왜 이 방식이 빠른가

검색으로 예제를 찾으면 남의 표에 맞춰진 수식이라 내 표에 옮기다 어긋납니다. 말로 시키면 처음부터 내 표 기준으로 나옵니다. 그 차이가 반나절과 1분을 가릅니다.

조회 함수 셋, 상황만 골라 말하기

다른 표에서 값을 찾아오는 함수는 셋입니다. 이름은 몰라도 되고, 상황만 구분해서 말하면 AI가 알아서 고릅니다.

VLOOKUP은 언제 쓰나

찾을 값이 표의 맨 왼쪽 열에 있고 그 오른쪽에서 값을 가져올 때 씁니다. 가장 흔한 상황이라 별말 없이 "코드로 단가를 찾아와"라고만 해도 이 함수가 나옵니다. 단, 왼쪽 열밖에 기준으로 못 잡는 제약이 있습니다.

INDEX+MATCH는 언제 쓰나

찾을 기준이 가져올 값보다 오른쪽에 있을 때, 즉 VLOOKUP이 막히는 상황에서 씁니다. "이름으로 사번을 찾고 싶은데 사번이 이름보다 왼쪽 열에 있어"라고 하면 이렇게 돌아옵니다.

=INDEX(A:A, MATCH(D2, B:B, 0))

VLOOKUP으로는 안 되는 방향을 AI가 알아서 우회한 겁니다. 우리는 방향 제약을 몰라도 됩니다.

XLOOKUP은 언제 쓰나

Microsoft 365나 최신 엑셀을 쓴다면 XLOOKUP 하나로 위 둘을 대체합니다. 좌우 방향을 안 가리고, 못 찾았을 때 표시할 값도 지정합니다.

=XLOOKUP(B2, 단가표!A:A, 단가표!C:C, "코드없음")

버전이 걸리면 "내 엑셀은 2019 버전이야. XLOOKUP 대신 되는 수식으로 줘"라고 덧붙이면 됩니다.

표로 정리하면

조건이 얽힐수록 말이 더 편하다

조건에 따라 다른 값을 넣는 IF 계열은 손으로 짜면 괄호에서 실수가 나지만, 말로 풀면 오히려 쉽습니다.

단순 IF부터

"80점 넘으면 합격, 아니면 불합격"처럼 갈림길이 하나면 IF 하나로 끝납니다. AI는 이런 걸 실수 없이 내놓습니다.

조건이 여럿이면 AND·OR가 알아서 들어간다

조건을 나란히 말하기만 하면 됩니다.

D열 부서가 '영업'이고 E열 실적이 100 이상인 행만 '우수'로 표시하고, 나머지는 빈칸으로 둬. 수식으로.

돌아오는 답은 =IF(AND(D2="영업", E2>=100), "우수", "")입니다. AND를 넣으라고 말한 적 없는데 알아서 들어갔습니다.

등급을 여러 단계로 나눌 때

구간이 셋 이상이면 중첩 IF나 IFS를 씁니다. "여기에 실적 200 넘으면 '최우수'도 추가해줘"처럼 뒤에 말을 얹으면 AI가 구조를 고쳐 줍니다.

실적이 200 이상이면 '최우수', 100 이상이면 '우수', 그 아래는 '보통'으로 등급을 나누는 수식. IFS 함수로 부탁해.

이렇게 하면 =IFS(E2>=200,"최우수", E2>=100,"우수", TRUE,"보통")가 나옵니다. 괄호를 몇 번 여닫는지 셀 필요가 없습니다.

조건과 집계를 함께 걸 때

"영업부 매출만 합쳐"라고 하면 SUMIF가, "영업부이면서 3월 것만 합쳐"라고 하면 SUMIFS가 나옵니다. 조건을 문장으로 늘어놓는 것만으로 함수가 갈립니다.

AI가 조용히 틀리는 자리는 정해져 있다

여기서 1장의 당부가 다시 나옵니다. 준 수식을 그대로 붙여넣고 끝내면 안 됩니다. 사고는 늘 같은 몇 군데에서 반복됩니다.

참조 범위가 짧다

AI가 범위를 A2:B10으로 잡았는데 실제 데이터가 500행까지면, 11행 아래는 조용히 빈값이나 오답이 됩니다. 표가 커질수록 눈에 안 띕니다.

절대참조 $가 빠졌다

$가 없으면 수식을 아래로 끌 때 참조 범위가 한 칸씩 밀립니다. 위쪽은 맞고 아래쪽만 틀리는, 제일 놓치기 쉬운 형태입니다.

숫자가 텍스트로 들어가 있다

코드가 "007"처럼 텍스트면 숫자 7과 안 맞아 #N/A가 납니다. 데이터 쪽 문제인데 수식을 의심하다 시간을 버립니다.

공통점은 "조용히" 틀린다는 것

셋 다 겉보기엔 멀쩡한데 숫자만 슬며시 어긋납니다. 대충 훑고 넘기면 못 잡습니다. 그래서 검증을 습관으로 박아 둬야 합니다.

준 수식은 반드시 되물어라

붙여넣기 전에 한 번 되묻는 습관이 사고를 대부분 막습니다.

각 부분을 설명시키기

방금 준 수식에서 각 부분이 무슨 역할인지 한 줄씩 설명해줘. 그리고 참조 범위가 내 데이터 전체(2행~500행)를 덮는지, 아래로 끌어도 범위가 안 밀리는지 확인해줘.

설명을 시키면 AI가 스스로 범위나 절대참조의 허점을 잡아내는 경우가 많습니다. 자기 답을 검토하게 만드는 셈입니다.

예시 세 줄로 대조하기

직접 값 두세 개로 결과를 맞춰 보세요. 손으로 아는 정답 한 줄과 수식 결과가 같은지만 봐도 충분합니다. "이 세 행의 결과가 이렇게 나와야 맞는데, 수식이 그렇게 나오는지 확인해줘"라고 던지면 됩니다.

원본은 복사본에 먼저

되돌리기가 애매한 작업은 복사본에서 먼저 돌려 보세요. 수식은 지우면 그만이지만, 값으로 붙여넣은 뒤엔 원본이 이미 바뀐 상태입니다.

검증을 더 깊이 다루는 곳

이 검증을 체계적으로 정리한 건 8장에서 따로 다룹니다. 자주 나오는 함정과 확인 체크리스트가 그쪽에 모여 있습니다.

다음 챕터에서는 지저분하게 섞인 데이터를 취합·분류·표준화·중복제거로 정리하는 법을 다루겠습니다.