엑셀에서 특정 값 입력 시 자동으로 다른 데이터 입력하는 방법

엑셀을 활용하면 특정 값을 입력했을 때 자동으로 관련 데이터를 불러오는 기능을 만들 수 있습니다. 예를 들어, 상품 코드 입력 시 자동으로 상품명이 입력되거나, 사번 입력 시 자동으로 직원 이름이 표시되도록 설정할 수 있습니다. 이를 통해 업무 효율성을 크게 높일 수 있습니다.

이번 포스팅에서는 VLOOKUP, INDIRECT, OFFSET 함수를 활용하여 데이터를 자동 입력하는 방법을 단계별로 설명해 드리겠습니다! 😊

 

[목차]

---

1. 엑셀 자동 입력 기능이 필요한 경우

✅ 자동 입력이 필요한 대표적인 사례

1. 상품 코드 입력 시 자동으로 상품명 입력
2. 사번 입력 시 직원 이름 자동 표시
3. 과목 코드를 입력하면 과목명 자동 입력
4. 학생 이름을 입력하면 담임 선생님 이름 자동 입력 (실제 예제 포함)
5. 거래처 코드를 입력하면 거래처명 자동 표시

이를 해결하기 위해 VLOOKUP(), INDIRECT(), OFFSET() 함수를 활용할 수 있습니다. 가장 간단한 방법부터 차례대로 알아보겠습니다.

 

---

2. VLOOKUP 함수로 자동 입력하기 (가장 쉬운 방법)

✅ VLOOKUP 함수란?

VLOOKUP() 함수는 특정 값을 기준으로 해당하는 데이터를 찾아주는 함수입니다. 상품 코드, 사번, 학생 이름 등을 입력하면 자동으로 관련된 정보를 불러올 수 있습니다.

🔹 VLOOKUP 기본 공식

=VLOOKUP(A2, Sheet2!$A:$B, 2, FALSE)

✅ 설명:

• A2 → 입력한 값 (예: 상품 코드, 학생 이름)
• Sheet2!$A:$B → 참조할 데이터가 있는 범위를 절대 참조로 설정하여 복사 시 범위가 변경되지 않도록 함
• 2 → 가져올 데이터가 있는 열의 번호
• FALSE → 정확히 일치하는 값만 찾도록 설정

 

🔹 예제 1: 상품 코드 입력 시 상품명 자동 입력

=VLOOKUP(A2,상품정보!A2:B4,2,FALSE)

 

✅ 결과: A101 입력 시 노트북이 자동으로 입력됨

📌 가장 쉽고 빠르게 적용할 수 있는 방법입니다!

---

3. INDIRECT 함수로 자동 입력하기 (동적 범위 설정)

✅ INDIRECT 함수란?

INDIRECT() 함수는 셀 주소나 범위를 동적으로 변경할 수 있는 함수입니다. 만약 시트 이름이 자주 변경되거나, 여러 개의 시트에서 데이터를 가져와야 할 경우 유용합니다.

🔹 예제 2: 여러 시트에서 상품명 자동 입력

=VLOOKUP(A2, INDIRECT("'상품정보'!$A:$B"), 2, FALSE)

✅ 설명:

• INDIRECT("'상품정보'!$A:$B") → '상품정보' 시트의 A:B 범위를 참조 (절대 참조 적용)
• VLOOKUP(A2, ..., 2, FALSE) → 입력한 값(A2)을 기준으로 상품명 불러오기

📌 여러 개의 시트에서 데이터를 불러와야 할 때 유용한 방법입니다.

---

4. OFFSET 함수로 자동 입력하기 (고급 방법)

✅ OFFSET 함수란?

OFFSET() 함수는 특정 셀을 기준으로 원하는 값을 동적으로 가져올 수 있는 함수입니다. 데이터 범위가 계속 변하는 경우 유용합니다.

🔹 예제 3: 학생 이름 입력 시 담임 선생님 자동 입력

 

=OFFSET(Sheet2!$A$1, MATCH(A2, Sheet2!$A:$A, 0)-1, 1)

✅ 설명:

• MATCH(A2, Sheet2!$A:$A, 0) → Sheet2에서 학생 이름이 있는 행 찾기 (절대 참조 적용)
• OFFSET(Sheet2!$A$1, ... -1, 1) → 해당 행에서 1열 오른쪽(담임 선생님) 값 가져오기

📌 데이터 범위가 계속 변하는 경우 유용하지만, 속도 문제로 인해 VLOOKUP보다 추천되지는 않습니다.

---

5. 어떤 방법을 선택할까?

📌 가장 추천하는 방법:

=VLOOKUP(A2, Sheet2!$A:$B, 2, FALSE)

✅ 이제 특정 값을 입력하면 자동으로 관련 정보가 입력됩니다! 🚀

---

 

6. 결론

엑셀을 활용하면 간단한 함수만으로도 데이터를 자동으로 불러올 수 있습니다. VLOOKUP, INDIRECT, OFFSET을 활용하여 상품 코드, 사번, 학생 이름 등을 입력하면 자동으로 관련 데이터를 불러올 수 있도록 설정할 수 있습니다.

✅ VLOOKUP: 가장 쉽고 빠른 방법 (추천!)
✅ INDIRECT: 여러 시트를 참조해야 할 때 유용
✅ OFFSET: 동적 범위를 사용할 때 활용 가능

이제 이 방법을 활용하여 엑셀 자동 입력 기능을 쉽게 구현해 보세요! 😊