ChatGPT 엑셀 함수 자동 생성 방법｜VLOOKUP·IF·INDEX MATCH 쉽게 만들기

엑셀로 매출을 정리하거나 재고를 관리하다 보면 막히는 순간이 있습니다.

상품코드를 입력하면 가격이 자동으로 나타나게 하고 싶거나, 재고가 일정 수량 이하로 떨어지면 경고 문구를 표시하고 싶은 경우가 대표적입니다.

이럴 때 필요한 것이 VLOOKUP이나 IF 같은 엑셀 함수입니다. 문제는 함수 이름은 들어봤어도 어떤 수식을 입력해야 할지 모르는 경우가 많다는 점입니다.

이제는 복잡한 함수의 구조를 처음부터 외우지 않아도 됩니다.

ChatGPT에 데이터가 어떻게 구성되어 있는지, 어떤 결과를 원하는지만 설명하면 필요한 엑셀 수식을 제안받을 수 있습니다.

다만 AI가 만든 수식이 언제나 정확한 것은 아닙니다. 실제 데이터에 적용한 다음 결과를 검증하는 과정이 필요합니다.

이번 글에서는 ChatGPT로 엑셀 함수를 만드는 방법을 4단계로 정리하고, VLOOKUP·IF·INDEX MATCH 함수를 실제 예제로 설명하겠습니다.

ChatGPT로 엑셀 함수를 만들 수 있을까요?

가능합니다.

ChatGPT는 사용자가 설명한 작업 조건을 바탕으로 엑셀 함수식을 작성하고, 수식에 들어간 각 요소의 역할을 설명할 수 있습니다.

예를 들어 다음과 같이 요청할 수 있습니다.

“상품코드를 입력하면 상품명과 판매가격이 자동으로 표시되는 엑셀 수식을 만들어 주세요.”

이때 ChatGPT에 상품코드가 어느 열에 있고 상품명과 가격이 각각 어느 열에 있는지 알려주면 더 정확한 수식을 만들 수 있습니다.

기존에 작성된 복잡한 수식을 붙여 넣고 무슨 의미인지 설명해 달라고 요청하는 것도 가능합니다.

엑셀 함수 자동 생성에 필요한 과정은 크게 네 단계입니다.

데이터 구조 입력 → 원하는 결과 설명 → 함수 생성 → 오류 검증

이 순서만 이해해도 엑셀 업무에 ChatGPT를 활용하기가 훨씬 수월해집니다.

1단계. 엑셀 데이터 구조를 ChatGPT에 알려주세요

수식을 만들기 전에 먼저 엑셀 데이터가 어떤 형태인지 설명해야 합니다.

이번에는 작은 온라인 쇼핑몰의 상품 재고표를 예제로 사용하겠습니다.

상품코드	상품명	단가(원)	재고(개)
P001	키보드	35,000	12
P002	마우스	18,000	3
P003	모니터	215,000	8
P004	USB 허브	29,000	0

이 표가 엑셀의 A1 범위에 있다고 가정하겠습니다.

A열에는 상품코드, B열에는 상품명, C열에는 단가, D열에는 재고 수량이 들어 있습니다.

ChatGPT에는 다음과 같이 입력하면 됩니다.

ChatGPT 입력 예시

엑셀에 상품 재고 데이터가 있습니다.

A열: 상품코드
B열: 상품명
C열: 단가
D열: 재고 수량

데이터는 2행부터 5행까지 있습니다.

상품코드를 기준으로 상품명이나
단가를 찾을 수 있는 엑셀 함수를
만들고 싶습니다.

초보자도 이해할 수 있도록
수식과 각 부분의 의미를
함께 설명해 주세요.

처음부터 엑셀 파일 전체를 업로드할 필요는 없습니다.

열 제목과 몇 개의 샘플 데이터만으로도 기본적인 함수 구조를 설명받을 수 있습니다.

데이터가 복잡한 경우에는 엑셀 파일을 첨부해 시트 구조를 확인하도록 요청할 수도 있습니다. 다만 사용 가능한 파일 분석 기능은 계정과 서비스 환경에 따라 달라질 수 있습니다.

2단계. 원하는 결과를 구체적으로 설명하세요

데이터 구조를 알려주었다면 이제 무엇을 계산할지 설명해야 합니다.

여기서 중요한 것은 단순히 “엑셀 함수를 만들어 주세요”라고 요청하지 않는 것입니다.

어느 셀의 값을 기준으로 무엇을 찾아야 하는지 구체적으로 알려주는 편이 좋습니다.

예를 들어 상품코드로 가격을 찾으려면 다음과 같이 요청합니다.

B3 셀에 상품코드를 입력하면

'실습데이터' 시트의 A열에서
같은 상품코드를 찾아

C열에 있는 단가를 반환하는
VLOOKUP 함수를 만들어 주세요.

정확히 일치하는 상품코드만
검색해야 합니다.

이처럼 검색 기준, 데이터 위치, 반환할 결과, 일치 조건을 알려주면 수식의 오류 가능성을 줄일 수 있습니다.

특히 VLOOKUP 함수에서는 정확히 일치하는 값을 찾아야 하는 경우가 많습니다.

상품코드처럼 고유한 값을 검색할 때는 일반적으로 정확한 일치 옵션인 FALSE를 사용하는 것이 적절합니다.

3단계. ChatGPT로 실제 엑셀 함수 생성하기

이제 대표적인 세 가지 함수를 실제로 만들어 보겠습니다.

예제 1. VLOOKUP 함수로 상품 가격 자동 조회하기

VLOOKUP은 특정 값을 기준으로 표에서 원하는 정보를 찾아오는 함수입니다.

상품코드를 이용해 가격을 조회하거나 직원번호로 직원 정보를 찾을 때 활용할 수 있습니다.

이번 실습에서는 ‘실습데이터’라는 시트에 상품 목록이 있고, 별도의 실습 시트 B3 셀에 검색할 상품코드가 입력되어 있다고 가정합니다.

실제 엑셀 수식

=VLOOKUP(B3,'실습데이터'!$A$2:$E$5,3,FALSE)

수식을 나누어 보면 이해하기 쉽습니다.

수식 구성	의미
B3	검색할 상품코드
‘실습데이터’!$A$2:$E$5	검색할 데이터 범위
3	범위의 세 번째 열인 단가를 반환
FALSE	정확히 일치하는 값만 검색

여기서 $ 기호는 절대참조를 의미합니다.

수식을 다른 셀로 복사하더라도 검색 범위가 바뀌지 않도록 고정하는 역할을 합니다.

B3 셀에 P002를 입력하면 마우스의 가격인 18,000원이 반환됩니다.

계산 결과

검색 상품코드	반환 결과
P002	18,000원

단, VLOOKUP은 지정된 검색 범위의 첫 번째 열에서 값을 찾습니다.

검색 기준 열보다 왼쪽에 있는 데이터를 반환해야 한다면 INDEX MATCH 또는 XLOOKUP 같은 방법을 검토하는 것이 좋습니다.

예제 2. IF 함수로 재고 부족 자동 표시하기

IF는 특정 조건이 맞는지 확인한 다음 서로 다른 결과를 표시하는 함수입니다.

재고 관리뿐 아니라 매출 목표 달성 여부, 예산 초과 여부, 직원 근무 상태 등을 구분할 때도 사용할 수 있습니다.

이번에는 재고가 5개 이하이면 ‘재고확인’, 6개 이상이면 ‘정상’이라는 문구가 나타나게 만들어 보겠습니다.

ChatGPT 요청 예시

D열에 상품 재고 수량이 있습니다.

재고가 5개 이하이면
"재고확인"이라고 표시하고,

6개 이상이면
"정상"이라고 표시하는
IF 함수를 만들어 주세요.

E2 셀부터 적용하겠습니다.

실제 엑셀 수식

=IF(D2<=5,"재고확인","정상")

수식을 해석하면 간단합니다.

D2의 값이 5 이하인지 확인한 다음 조건이 맞으면 ‘재고확인’, 그렇지 않으면 ‘정상’을 반환합니다.

계산 결과

상품명	재고 수량	결과
키보드	12	정상
마우스	3	재고확인
모니터	8	정상
USB 허브	0	재고확인

이 수식을 E2 셀에 입력하고 아래쪽으로 복사하면 각 상품의 재고 상태를 자동으로 구분할 수 있습니다.

특히 여러 상품을 관리하는 소규모 쇼핑몰에서는 재고 확인에 활용하기 좋은 방식입니다.

예제 3. INDEX MATCH로 상품 가격 검색하기

INDEX와 MATCH는 서로 다른 기능을 가진 두 함수를 결합하는 방식입니다.

MATCH는 검색값이 지정한 범위에서 몇 번째 위치에 있는지를 찾습니다.

INDEX는 지정한 범위의 특정 위치에 있는 값을 반환합니다.

두 함수를 결합하면 원하는 상품의 위치를 찾고, 같은 위치에 있는 가격을 가져올 수 있습니다.

ChatGPT 요청 예시

상품코드를 기준으로
상품 가격을 검색하려고 합니다.

상품코드는 '실습데이터'
시트의 A2:A5에 있고,

단가는 C2:C5에 있습니다.

현재 시트의 B3 셀에
상품코드를 입력하면

해당 단가가 반환되는
INDEX MATCH 수식을 만들어 주세요.

실제 엑셀 수식

=INDEX('실습데이터'!$C$2:$C$5,MATCH(B3,'실습데이터'!$A$2:$A$5,0))

처음 보면 복잡해 보이지만 구조는 어렵지 않습니다.

MATCH는 B3에 입력된 상품코드가 A2 범위에서 몇 번째인지 찾습니다.

그 위치를 INDEX에 전달하면 C2 범위에서 같은 순서에 있는 단가를 가져옵니다.

B3에 P003을 입력했다면 결과는 다음과 같습니다.

상품코드	상품명	검색 결과
P003	모니터	215,000원

INDEX MATCH의 장점은 검색할 열과 결과를 반환할 열을 별도로 지정할 수 있다는 것입니다.

다만 최신 Excel 환경에서는 같은 작업을 더 간단하게 처리할 수 있는 XLOOKUP도 제공됩니다.

새로 수식을 작성한다면 사용 중인 엑셀 버전과 유지보수 편의성을 고려해 함수를 선택하는 것이 좋습니다.

4단계. ChatGPT가 만든 엑셀 함수 오류 검증하기

ChatGPT가 제안한 수식은 실제 엑셀에서 확인해야 합니다.

함수 이름이 맞아도 셀 위치를 잘못 지정하거나 검색 범위를 잘못 설정하면 결과가 달라질 수 있기 때문입니다.

우선 정상적으로 작동하는 데이터로 결과를 확인합니다.

그다음 존재하지 않는 상품코드를 입력하는 등 예외 상황도 점검하는 것이 좋습니다.

엑셀에서 자주 발생하는 오류는 다음과 같습니다.

오류	주요 원인	확인할 사항
#N/A	일치하는 검색값이 없음	상품코드, 공백, 데이터 형식
#VALUE!	인수 또는 데이터 형식 문제	숫자·텍스트 형식, 함수 인수
#REF!	잘못된 셀 참조	삭제된 행·열, 참조 범위
#NAME?	함수 이름 등을 인식하지 못함	함수 철자, 따옴표, 정의된 이름

예를 들어 VLOOKUP으로 존재하지 않는 상품코드를 찾으면 #N/A 오류가 표시될 수 있습니다.

이때 IFERROR 함수를 함께 사용하면 오류 발생 시 원하는 메시지를 표시할 수 있습니다.

=IFERROR(VLOOKUP(B3,'실습데이터'!$A$2:$E$5,3,FALSE),"상품없음")

검색값을 찾지 못하거나 해당 수식에서 다른 오류가 발생하면 ‘상품없음’이라는 문구가 나타납니다.

하지만 주의할 점도 있습니다.

IFERROR는 오류의 원인을 해결하는 함수가 아니라 오류가 발생했을 때 다른 결과를 표시하는 함수입니다.

검색 범위가 잘못되었는데도 오류가 감춰지면 문제를 발견하기 어려울 수 있습니다.

따라서 수식이 정확하게 작동하는지 먼저 확인한 후 필요한 경우에만 IFERROR를 적용하는 것이 좋습니다.

오류 해결이 어렵다면 ChatGPT에 현재 수식과 오류 메시지를 함께 전달해 원인을 분석하도록 요청할 수 있습니다.

복사해서 사용하는 ChatGPT 엑셀 함수 생성 프롬프트

다음 프롬프트는 VLOOKUP뿐 아니라 IF, INDEX MATCH 등 다른 엑셀 함수를 만들 때도 응용할 수 있습니다.

당신은 엑셀 수식 작성 도우미입니다.

다음 엑셀 데이터 구조와 작업 조건을
분석하여 적절한 함수를 만들어 주세요.

[데이터 구조]
시트 이름:
열 제목:
데이터 범위:

[원하는 작업]
기준이 되는 셀:
찾거나 계산할 값:
결과를 표시할 셀:
적용해야 할 조건:

[작성 조건]
1. 실제 사용할 엑셀 수식을 작성하세요.
2. 각 함수와 인수의 의미를 설명하세요.
3. 상대참조와 절대참조를 구분하세요.
4. 예상 계산 결과를 예시로 제시하세요.
5. 발생 가능한 오류와 해결 방법을 알려주세요.
6. 가능하면 더 간단한 대체 함수도 제안하세요.
7. 데이터 구조가 불명확하면 추측하지 말고
   확인해야 할 내용을 알려주세요.

여기에 자신의 데이터 구조와 작업 조건을 입력하면 됩니다.

이미 만들어진 수식이 너무 복잡하다면 “이 수식을 초보자도 이해할 수 있도록 단계별로 설명해 주세요”라고 요청해 보세요.

수식 자체를 만드는 것뿐 아니라 기존 업무 파일을 이해하는 데도 도움이 됩니다.

엑셀 파일을 ChatGPT에 직접 올려도 될까요?

ChatGPT의 데이터 분석 기능은 엑셀 파일을 비롯한 여러 형식의 데이터를 처리할 수 있습니다.

복잡한 데이터라면 파일을 첨부한 뒤 어떤 시트를 분석할지, 어떤 결과를 원하는지 구체적으로 설명하는 방법도 있습니다.

또한 지원되는 환경에서는 Excel 안에서 ChatGPT 추가 기능을 이용해 스프레드시트를 작성하거나 수정할 수도 있습니다.

다만 실제 회사 자료를 사용할 때는 개인정보와 민감한 사업정보에 주의해야 합니다.

고객 이름, 연락처, 주민등록번호, 거래처의 비공개 가격, 계좌번호 등은 필요한 경우 익명화하거나 제거하는 것이 좋습니다.

실제 데이터를 업로드하지 않고 열 구조와 가상의 샘플 데이터만 설명해도 수식 생성이 가능한 경우가 많습니다.

특히 중요한 매출·회계·재고 데이터에는 AI가 제안한 수식을 바로 적용하기보다 복사본에서 먼저 시험해 보는 것이 안전합니다.

어떤 엑셀 함수를 선택하는 것이 좋을까요?

작업 목적	추천 함수
상품코드로 가격 조회	VLOOKUP
조건에 따라 결과 구분	IF
검색 열과 반환 열을 자유롭게 지정	INDEX MATCH
조건에 맞는 데이터 개수 계산	COUNTIF
조건에 맞는 금액 합산	SUMIF
검색 오류 발생 시 메시지 표시	IFERROR
최신 엑셀에서 유연한 값 검색	XLOOKUP

중요한 것은 어떤 함수가 가장 복잡하거나 강력한지가 아닙니다.

자신의 데이터 구조와 작업 목적에 맞으면서, 나중에 수정하기 쉬운 함수를 선택하는 편이 좋습니다.

마무리: 엑셀 함수보다 중요한 것은 결과를 확인하는 습관입니다

ChatGPT를 활용하면 VLOOKUP이나 INDEX MATCH 같은 함수를 처음부터 외우지 않아도 필요한 수식을 만드는 데 도움을 받을 수 있습니다.

업무에 필요한 조건을 자연어로 설명하고, 생성된 수식을 복사해 엑셀에 입력하면 됩니다.

다만 수식이 오류 없이 실행된다고 해서 계산 결과까지 정확하다는 뜻은 아닙니다.

검색 범위가 잘못되었거나 다른 열을 반환하도록 설정되어 있어도 정상적인 숫자가 나타날 수 있습니다.

따라서 데이터 구조를 정확히 알려주고, 원하는 결과를 구체적으로 설명하고, 실제 계산 결과를 확인하는 과정이 중요합니다.

처음에는 간단한 재고표나 매출표를 이용해 연습해 보세요.

반복해서 사용하다 보면 단순한 함수 작성뿐 아니라 기존 수식 분석과 데이터 관리에도 ChatGPT를 활용할 수 있습니다.