아파트 VLOOKUP 다중조건 엑셀 공식: 동호수 결합과 INDEX MATCH 대체 서식

관리사무소에서 수납 장부나 중간관리비 영수증을 작성할 때 가장 많이 쓰는 함수가 VLOOKUP입니다.

하지만 아파트 관리 실무 데이터는 '101동 101호', '102동 101호'처럼 같은 호수를 쓰는 세대가 동마다 반복됩니다. 일반적인 VLOOKUP 수식에 호수만 넣고 검색을 돌리면, 수식은 언제나 표의 가장 위에 있는 101동 101호의 금액만 끌고 오는 치명적인 오류를 냅니다.

이를 해결하겠다고 동과 호수를 계산기로 일일이 확인하며 수기로 타이핑하다 보면 중간관리비 정산이나 미수금 대조 과정에서 다른 세대 금액을 청구하는 사고가 터집니다.

복잡한 매크로(VBA) 없이 엑셀 기본 기능인 '보조열 고유값(텍스트 결합)'과 'INDEX MATCH 다중조건 배열 수식'을 활용해 동과 호수 두 가지 조건을 완벽히 일치시키는 실전 공식 2가지를 정리해 드립니다.

1. VLOOKUP 다중조건 해결 방식 2가지 비교

현장 상황과 시트 구조에 맞게 선택할 수 있는 두 가지 방식입니다.

비교 항목	1안: 보조열(& 결합) 생성 방식	2안: INDEX MATCH 배열 수식 방식
적용 난이도	초보자도 10초 만에 세팅 가능 (가장 추천)	수식 구조에 대한 기본 이해 필요
시트 수정 여부	원본 데이터 맨 왼쪽에 열 1개 추가 필요	원본 표의 구조 변경 없이 그대로 적용 가능
연산 속도	1,000세대 이상 대단지에서도 속도 매우 빠름	데이터가 수천 행 이상일 경우 미세한 연산 지연
수식 구조	=VLOOKUP(조회동&조회호, 참조범위, 열번호, 0)	=INDEX(가져올범위, MATCH(1, (동범위=동)*(호범위=호), 0))

2. 1안: 가장 안전한 '보조열 고유값(&)' 결합 수식

원본 데이터 표의 구조를 한 열 정도 수정할 수 있다면 이 방식이 가장 오류가 적고 직관적입니다.

① 원본 시트에 고유 키(Key) 만들기

• 원본 데이터 표(예: 관리비 수납 내역 시트)의 A열에 빈 열을 하나 삽입합니다.
• A2 셀에 다음 수식을 넣고 아래로 끝까지 복사합니다:
• =B2&"-"&C2 (B열이 동, C열이 호수일 때)
• 이렇게 하면 '101-101', '102-101'처럼 중복이 전혀 발생하지 않는 세상에 단 하나뿐인 고유 텍스트가 생성됩니다.

② 조회 시트에서 VLOOKUP 다중조건 불러오기

• 중간관리비 영수증이나 조회 화면에서 찾고자 하는 동이 A5 셀, 호수가 B5 셀에 적혀 있다면, 수식을 다음과 같이 작성합니다:
• =VLOOKUP(A5&"-"&B5, '수납내역'!$A$2:$F$1000, 4, FALSE)
• 해설: 찾을 값 자리에 A5&"-"&B5를 넣어 '101-101'을 만들고, 보조열이 포함된 원본 시트 범위를 지정해 4번째 열(당월 부과액)을 정확하게 불러옵니다.

3. 2안: 원본 표를 건드리지 않는 'INDEX MATCH' 공식

본사 보고 양식이거나 프로그램에서 다운로드한 파일이라 원본 표에 열을 추가할 수 없을 때 사용하는 공식입니다.

원본 시트에서 동이 A2:A500, 호수가 B2:B500, 가져올 관리비 금액이 D2:D500에 있고, 내가 조회하려는 동이 F2 셀, 호수가 G2 셀에 있을 때 걸어주는 공식입니다.

=INDEX($D$2:$D$500, MATCH(1, ($A$2:$A$500=F2) * ($B$2:$B$500=G2), 0))

• 해설:
  • ($A$2:$A$500=F2) * ($B$2:$B$500=G2)는 동과 호수가 동시에 일치하는 행을 찾아 논리값 TRUE * TRUE = 1을 반환합니다.
  • MATCH 함수가 숫자 1이 있는 정확한 행 번호를 찾아내고, INDEX 함수가 D열의 해당 행에 있는 금액을 오차 없이 뽑아냅니다.
  • 엑셀 2019 이전 구버전 사용자라면 수식 입력 후 일반 엔터가 아닌 Ctrl + Shift + Enter를 함께 눌러 배열 수식으로 마감해야 정상 작동합니다. (최신 엑셀 및 M365는 일반 엔터로 작동)

4. 수식 적용 시 자주 발생하는 에러와 해결법

• #N/A 에러 (값을 찾을 수 없음):
• 동이나 호수 뒤에 눈에 보이지 않는 스페이스바(공백)가 들어가 있는 경우가 90%입니다. 원본 데이터의 텍스트 양쪽 공백을 제거하기 위해 수식에 TRIM 함수를 감싸주거나(=TRIM(B2)&"-"&TRIM(C2)), '찾기 및 바꾸기(Ctrl+H)'로 공백을 일괄 제거해야 합니다.
• 숫자와 텍스트 형식 불일치:
• 조회 화면의 동호수는 '숫자' 형태인데, ERP 전산에서 긁어온 원본 데이터는 '텍스트' 형태로 저장되어 있으면 서로 다른 값으로 인식해 에러가 납니다. 한쪽 셀에 *1을 곱해주거나 전체 열의 데이터 서식을 [일반]으로 통일시켜 줍니다.

아파트 행정 데이터 관리의 핵심은 "중복되는 호수 앞에 동 번호를 묶어 고유한 기준점을 만드는 것"입니다.

보조열 방식이나 INDEX MATCH 수식 중 단지 장부 구조에 맞는 공식 하나만 제대로 심어두어도, 매달 반복되는 수납 대조와 중간관리비 정산 업무를 클릭 한 번으로 안전하게 끝낼 수 있습니다.