VLOOKUP INDEX MATCH 함수를 활용한 방법

VLOOKUP, INDEX, MATCH 함수를 활용한 방법

 

특정 조건(예: 품명, 규격, 구경 등)에 따라 자재 단가를 찾기 위한 함수를 만들어보겠습니다. 데이터는 A1:D7에 있다고 가정하고, VLOOKUP, INDEX, MATCH 함수를 활용한 방법을 단계별로 설명드리겠습니다.

 

---

데이터 구조

ABCD

품명	품명	규격	자재 단가
STS304	PIPE SCH10S	65A	164,070
STS304	PIPE SCH10S	40A	100,050
STS304	ELBOW SCH10S	65A	12,510
STS304	ELBOW SCH10S	40A	5,940
STS304	TEE SCH10S	65A+15A	24,827
STS304	TEE SCH10S	40A+40A	13,464

---

목표

• 특정 품명, 품명(PIPE, ELBOW, TEE 등), 규격(예: 65A, 40A+40A 등)에 따라 자재 단가를 찾고 싶습니다.
• 예를 들어, "STS304", "ELBOW SCH10S", "40A"에 해당하는 자재 단가(5,940)를 찾아야 합니다.

---

1. VLOOKUP으로 해결 (단순하지만 제약 있음)

VLOOKUP은 단일 열을 기준으로 검색해야 하므로, 여러 조건(품명, 품명, 규격)을 모두 처리하기 어렵습니다. 하지만 특정 열(예: B열)을 기준으로 검색하는 간단한 경우에 사용할 수 있습니다.

예시: "ELBOW SCH10S"의 자재 단가 찾기

• 구문: =VLOOKUP("ELBOW SCH10S", B2:D7, 3, FALSE)
  • B열에서 "ELBOW SCH10S"를 찾고, 3번째 열(자재 단가)을 반환.
• 결과: 첫 번째 매칭되는 "ELBOW SCH10S"의 자재 단가(12,510, 65A 기준).
• 문제점:
  • 규격(65A, 40A 등)을 고려하지 못함.
  • 여러 조건(품명 + 품명 + 규격)을 동시에 처리하기 어려움.

---

2. INDEX + MATCH로 여러 조건 처리

INDEX와 MATCH를 조합하면 품명(A열), 품명(B열), 규격(C열)을 모두 고려해 자재 단가를 찾을 수 있습니다. 이를 위해 데이터 구조를 분석하고, 다중 조건 검색을 구현합니다.

단계 1: 데이터 준비

• A열(품명), B열(품명), C열(규격)이 키로 사용됨.
• D열(자재 단가)이 반환 값.

단계 2: 다중 조건 MATCH

품명, 품명, 규격을 모두 고려하려면 MATCH를 여러 번 사용하거나, 조건부 로직을 추가해야 합니다. 하지만 엑셀의 기본 함수만으로 다중 조건을 처리하려면 약간 복잡해질 수 있습니다. 대신, 간단히 두 조건(예: B열과 C열)을 기준으로 검색하는 방법을 제안합니다.

예시: "ELBOW SCH10S"와 "40A"의 자재 단가 찾기

1. MATCH로 행 위치 찾기
   • B열에서 "ELBOW SCH10S"의 위치 찾기:
     =MATCH("ELBOW SCH10S", B2:B7, 0)
     → 결과: 3 (B4에 있음).
   • C열에서 "40A"의 위치 찾기 (동일 행 확인 필요).
2. INDEX로 값 가져오기
   • B열과 C열에서 모두 일치하는 행을 찾아 D열 값 반환:
     =INDEX(D2:D7, MATCH(1, (B2:B7="ELBOW SCH10S")*(C2:C7="40A"), 0))
     • 주의: 이 공식은 배열 수식을 사용하며, 엑셀에서 Ctrl + Shift + Enter로 입력해야 합니다(최신 엑셀에서는 자동으로 동작).
     • 결과: 5,940 (D4의 값).

• 설명:
  • (B2:B7="ELBOW SCH10S")와 (C2:C7="40A")는 각각 TRUE/FALSE 배열을 생성.
  • * 연산으로 두 조건이 모두 TRUE인 행을 1로 표시.
  • MATCH(1, ...)으로 해당 행의 위치를 찾아 INDEX로 값을 반환.

---

3. 더 유연한 방법 (추가 조건 포함)

품명(A열)까지 고려하려면 공식이 더 복잡해집니다. 아래는 "STS304", "ELBOW SCH10S", "40A"를 모두 만족하는 자재 단가를 찾는 예시입니다:

공식

=INDEX(D2:D7, MATCH(1, (A2:A7="STS304")*(B2:B7="ELBOW SCH10S")*(C2:C7="40A"), 0))

• 결과: 5,940.
• 주의:
  • 이 공식은 배열 수식이며, 엑셀 버전에 따라 다르게 동작할 수 있음.
  • 최신 엑셀(Office 365, Excel 2021)에서는 자동으로 동작하지만, 이전 버전에서는 Ctrl + Shift + Enter 필요.

---

4. 실용적인 팁

• 오류 처리 추가: 값이 없으면 오류를 방지하려면 IFERROR 사용:
  =IFERROR(INDEX(D2:D7, MATCH(1, (A2:A7="STS304")*(B2:B7="ELBOW SCH10S")*(C2:C7="40A"), 0)), "찾을 수 없음")
• 동적 입력: 셀 참조를 사용해 조건을 변경 가능:
  • A1에 "STS304", B1에 "ELBOW SCH10S", C1에 "40A" 입력.
  • 공식:
    =IFERROR(INDEX(D2:D7, MATCH(1, (A2:A7=A1)*(B2:B7=B1)*(C2:C7=C1), 0)), "찾을 수 없음")

---

결론

• VLOOKUP: 단순한 단일 조건 검색에 적합하지만, 이 데이터처럼 여러 조건이 필요하면 한계가 있음.
• INDEX + MATCH: 다중 조건 검색에 훨씬 유연하고 강력함. 하지만 배열 수식 사용으로 약간 복잡할 수 있음.

궁금한 점이나 특정 조건에 맞춘 추가 예시가 필요하면 말씀해주세요! 엑셀 버전이나 더 구체적인 요구사항이 있다면 알려주시면 최적화된 솔루션을 제안드릴게요.