VLOOKUP 대신 INDEX+MATCH 조합해서 쓰기

 

많은 분들이 "찾기 함수" 하면 가장 먼저 VLOOKUP을 떠올립니다.

 

실제로 이름으로 부서를 찾거나,

사번으로 직급을 찾는 것처럼

"기준값 하나로 다른 정보를 끌어오는" 상황에서

VLOOKUP은 정말 자주 쓰입니다.

 

그런데 이 함수가

내가 문서 작업을 할 때, 빠르고 편하려고 쓰는건데

함수를 위해 데이터를 작업해야된다는 것이

저는 좀 불쾌했습니다.

 

그래서 방법을 찾았는데,

(이건 엑셀에서도 사용이 가능한 함수입니다.)

 

⚠️문제 상황: VLOOKUP이 갑자기 안 먹히는 순간

아래와 같은 직원 명단 시트가 있다고 해보겠습니다.

"이름으로 부서를 찾고 싶다"는 상황이라면

VLOOKUP으로 문제없이 해결됩니다.

=VLOOKUP(A2, F2:H4, 2, 0)

이렇게 쓰면,  F열(이름)에서 값을 찾아

오른쪽으로 두 번째 열인 G열(부서)의 값을 가져옵니다.

 

문제는 반대 상황입니다.

"이름으로 사번을 찾고 싶다"면 어떻게 될까요?

사번(E열)은 이름(F열)보다 왼쪽에 있습니다.

 

VLOOKUP은 구조상 항상 범위의 첫 번째 열에서 값을 찾고,

거기서 오른쪽 방향으로만 결과를 가져올 수 있습니다.

왼쪽 열의 값은 아예 가져올 방법이 없습니다.

 

이런 경험도 있으실 겁니다.

VLOOKUP을 잘 만들어놓고

몇 달 뒤에 팀에서 "입사일" 열을 부서와 직급 사이에 추가했다고 해보겠습니다.

그 순간 열 번호 2, 3 같은 식으로

숫자로 고정해뒀던 부분들이 전부 엉뚱한 값을 가리키게 됩니다.

 

열 하나 추가했을 뿐인데,

이미 만들어둔 수식들을 전부 손으로 다시 확인해야 하는 상황이 벌어지는 겁니다.

 

이 두 가지 문제,

즉 왼쪽 열을 찾지 못하는 것과 .

열이 추가되면 수식이 깨지는 것을

동시에 해결해주는 조합이

바로 INDEX와 MATCH입니다.

 

두 함수를 각각 이해한 다음,

왜 이 둘을 합치면 문제가 풀리는지 순서대로 보겠습니다.

INDEX 함수: "몇 번째 줄, 몇 번째 칸"을 좌표로 찍어주는 함수

INDEX는 INDEX(전체범위, 행 위치, 열 위치) 형태로 씁니다.

• 전체범위: 결과값을 가져올 큰 테이블입니다. 위 예시에서는 D1:H4 전체입니다.
• 행 위치: 그 범위 안에서 몇 번째 줄인지를 나타내는 숫자입니다.
• 열 위치: 그 범위 안에서 몇 번째 칸인지를 나타내는 숫자입니다.

예를 들어

=INDEX(E2:H4, 2, 3)

이라고 쓰면, E2:H4 범위에서

"2번째 줄, 3번째 칸"을 찾으라는 뜻이 됩니다.

 

2번째 줄은 이서연 님 행(3행)이고,

3번째 칸은 부서 열(E, F, G 순으로 세면 G가 3번째)이니,

결과는 "개발팀"이 나옵니다.

 

여기서 행 위치를 3으로 바꿔보면 어떻게 될까요?

=INDEX(A2:D4, 3, 3)은 3번째 줄(박지훈 님 행),

3번째 칸(부서)이니 결과가 "영업팀"으로 바뀝니다.

즉 행 위치 숫자 하나만 바뀌어도

결과가 통째로 달라진다는 걸 알 수 있습니다.

 

INDEX는 결국 "몇 번째 줄, 몇 번째 칸"이라는

좌표만 알면 그 자리의 값을 그대로 꺼내주는 함수입니다.

 

MATCH 함수: 값이 "몇 번째"에 있는지 찾아주는 함수

문제는 INDEX 혼자서는 "몇 번째 줄"인지를

사람이 직접 세어서 숫자로 입력해줘야 한다는 점입니다.

 

이름이 100명 있는 시트에서

"김민준이 몇 번째 줄이지?"를 매번

눈으로 세고 있을 수는 없습니다.

 

이걸 대신 찾아주는 함수가 MATCH입니다.

MATCH는 MATCH(찾을 값, 검색할 범위, 일치 유형) 형태로 씁니다.

• 찾을 값: 몇 번째에 있는지 알고 싶은 값입니다. 예: "이서연"
• 검색할 범위: 그 값을 찾을 한 줄짜리 또는 한 칸짜리 범위입니다. 예: F2:F4 (이름 열)
• 일치 유형: 정확히 일치하는 값을 찾을 것인지 여부입니다. 정확히 일치하는 값을 찾을 때는 0을 입력합니다.

=MATCH("이서연", F2:F4, 0)

이라고 쓰면,F2:F4 범위 안에서

"이서연"이 몇 번째에 있는지를 찾아 숫자 2를 돌려줍니다.

F2가 1번째, F3가 2번째이기 때문입니다.

 

 

여기서 찾는 이름을 "박지훈"으로 바꾸면

결과는 3으로 바뀝니다.

MATCH는 값 자체가 아니라 "몇 번째에 있는지"라는

위치 정보만 돌려준다는 점이 핵심입니다.

 

왜 이 둘을 합치면 문제가 풀리는가?

INDEX는 행 위치와 열 위치,

이 두 좌표를 숫자로 알아야 값을 꺼낼 수 있습니다.

 

그런데 MATCH는 이름이든 항목명이든 값 하나만 넣으면

그게 "몇 번째"인지 찾아줍니다.

그렇다면 행 위치를 구할 때도 MATCH,

열 위치를 구할 때도 MATCH를 쓰면,

좌표 두 개를 전부 자동으로 채워 넣을 수 있다는 이야기가 됩니다.

 

앞서 문제였던 "이름으로 사번 찾기"를 이 방식으로 풀어보면 이렇습니다.

=INDEX(D1:H4, MATCH(A2, F1:F4, 0), MATCH(B1, D1:H1, 0))

• 행 위치: MATCH("이서연", F1:F4, 0) → 이름 열(F1:F4)에서 이서연은 3번째이므로 결과는 3
• 열 위치: MATCH("사번", D1:H1, 0) → 머리글 행(D1:H1)에서 "사번"은 2번째 칸이므로 결과는 2
• INDEX(D1:H4, 3, 2) → 전체 범위에서 "3번째 줄, 2번째 칸" 값을 가져오라는 뜻이 되어, 최종 결과는 1002

이 구조가 좋은 이유는,

열 위치조차 숫자가 아니라 "사번"이라는

머리글 텍스트로 찾는다는 데 있습니다.

 

즉 "사번"이라는 이름표만 있으면,

그게 몇 번째 열로 옮겨가든 INDEX가 알아서 찾아가는 구조가 되는 겁니다.

 

여기에 찾고 싶은 항목명을 직접 입력하지 않고

셀 하나를 참조하도록 만들어두면,

그 셀 값을 "사번"에서 "직급"으로 바꾸기만 해도 결과가 자동으로 따라 바뀌는 유연한 수식이 됩니다.

 

실무 적용 사례

*본 데이터는 AI를 통해 임의로 생성한 데이터 입니다.

실제 업무에서는 한 명만 궁금한 게 아니라,

여러 사람의 여러 항목을 한꺼번에 정리해야 하는 경우가 훨씬 많습니다.

 

이럴 때 필요한 게 절대 참조($) 입니다.

수식을 다른 셀로 복사하면,

셀 주소는 복사되는 방향에 맞춰 자동으로 움직입니다.

그런데 직원 명단처럼

"항상 같은 자리에 있어야 하는 기준 범위"는

이렇게 움직이면 안 됩니다.

 

그래서 열 앞이나 행 앞에 $를 붙여서

"여기는 고정, 움직이지 마"라고 표시해줍니다.

100명 규모의 직원 명단(F1:J101)을 기준으로,

여러 명의 이름과 항목을 한 번에 조회하는 카드를 만들어보면 이렇습니다.

 

 

B3에 들어가는 수식은 다음과 같습니다.

=INDEX($F$1:$J$101, MATCH($A3, $H$1:$H$101, 0), MATCH(B$2, $F$1:$J$1, 0))

각 부분에 붙은 $의 역할을 하나씩 뜯어보면 이렇습니다.

• $F$1:$J$101 — 행과 열 모두 $로 고정했습니다. 전체 직원 명단은 이 수식을 어느 셀에 복사해도 항상 F1:J101 그 자리를 가리켜야 하기 때문입니다.
• $A3 — 열(A)만 고정하고 행(3)은 고정하지 않았습니다. 이 수식을 오른쪽(B3 → C3)으로 복사해도 이름은 항상 A열에서 가져와야 하니 열을 고정했고, 아래쪽(3행 → 4행)으로 복사하면 각 사람의 행에 맞는 이름을 가져와야 하니 행은 고정하지 않았습니다.
• B$2 — 반대로 행(2)만 고정하고 열(B)은 고정하지 않았습니다. 아래로 복사해도 항목명은 항상 2행(머리글)에서 가져와야 하니 행을 고정했고, 오른쪽으로 복사하면 옆 칸의 항목명(부서, 직급 등)을 가져와야 하니 열은 고정하지 않았습니다.

이렇게 만들어두면,

B3 셀 하나에만 수식을 입력한 뒤

그대로 드래그해서 오른쪽과 아래로 채우기만 하면 됩니다.

 

A열에 이름을 몇 명이든 추가로 적어 넣고,

2행에 조회하고 싶은 항목을 몇 개든 늘려두면,

기준이 되는 직원 명단($F$1:$J$101)은 흔들리지 않은 채로

이름과 항목 조합만 자동으로 바뀌면서

필요한 만큼 데이터를 뽑아낼 수 있습니다.

 

데이터가 100명이든 1,000명이든, 조회 카드의 크기만 늘리면 똑같이 작동합니다.

 

---

🔧 구글시트 꿀팁: 
구글 시트에는 패턴을 감지해서 값을 추천해주는 '스마트 입력' 기능이 있는데,
표나 범위에서 값을 찾아오는 작업에도 활용할 수 있습니다. 
B3에 수식을 넣고 B4에 같은 패턴으로 값을 채우기 시작하면, 
나머지 칸을 스마트 입력이 알아서 미리 채워 제안해주기도 합니다. 

다만 이건 지금처럼 INDEX와 MATCH가 왜 그 값을 가져오는지 원리를 이해한 다음에 보조 도구로 써야, 
제안된 값이 맞는지 아닌지도 스스로 판단할 수 있습니다.

#구글스프레드시트함수 #INDEX함수 #MATCH함수 #VLOOKUP한계 #구글시트함수 #엑셀함수조합 #절대참조 #직장인엑셀함수 #구글시트꿀팁 #끼리시트