VLOOKUP으로 안 풀리던 문제, 이 조합으로 해결됩니다

찾을 값이 있는 열보다 참조할 열이 왼쪽에 있으면 VLOOKUP은 답이 없습니다. 저도 한동안 "그럼 열 순서를 바꿔야 하나" 하면서 원본 표를 억지로 재배치하곤 했는데, 그게 오히려 더 위험한 방법이었어요. 다른 수식들이 이미 그 열 위치를 참조하고 있으면 하나 옮기는 순간 줄줄이 깨지거든요. 보고서 하나 고치려다 다른 시트 수식까지 다 손봐야 했던 날, 진짜 허탈했습니다.

그래서 찾아보다 알게 된 게 INDEX와 MATCH를 같이 쓰는 방법이었어요.

MATCH는 위치, INDEX는 값

MATCH는 "이 값이 범위 안에서 몇 번째 위치에 있는지"만 찾아줍니다. 그리고 INDEX는 "몇 번째 위치의 값을 가져와"라고 알려주는 역할이에요. 이 둘을 합치면 왼쪽이든 오른쪽이든, 찾는 열과 가져올 열의 위치 관계를 전혀 신경 쓰지 않아도 됩니다.

기본 구조는 이렇습니다.

=INDEX(가져올열, MATCH(찾을값, 찾을열, 0))

예를 들어 사번이 D열에 있고 이름이 A열에 있다고 해볼게요. VLOOKUP이라면 이름이 사번보다 왼쪽에 있어서 못 쓰는 상황인데, INDEX/MATCH는 순서를 전혀 신경 쓰지 않습니다.

=INDEX(A2:A100, MATCH(D2, D2:D100, 0))

D열에서 값을 찾아서, 같은 행에 있는 A열 값을 그대로 가져오는 방식입니다. 마지막에 있는 0은 "정확히 일치하는 값만 찾아라"는 뜻이니 꼭 넣어주세요. 빼먹으면 엉뚱한 값이 나올 수 있습니다.

진짜 차이는 열이 바뀔 때 드러납니다

처음엔 "그냥 VLOOKUP 쓰면 되는데 왜 굳이"라는 생각이 들 수 있어요. 저도 그랬거든요. 그런데 진짜 진가는 표 구조가 바뀔 때 나타납니다.

VLOOKUP은 몇 번째 열의 값을 가져올지 숫자로 지정합니다. 그런데 중간에 열 하나만 삽입되거나 삭제되면, 그 숫자가 그대로 남아있어서 엉뚱한 열의 값을 가져오는 사고가 생깁니다. 실제로 이것 때문에 보고서에 틀린 금액이 올라간 적도 있었어요. 반면 INDEX/MATCH는 범위 자체를 참조하기 때문에 중간에 열이 추가되거나 순서가 바뀌어도 결과가 흔들리지 않습니다.

부서마다 양식이 자주 바뀌거나, 여러 사람이 같은 파일을 돌아가며 수정하는 환경이라면 이 차이가 생각보다 큽니다. 한 번 세팅해두면 나중에 열 구조가 바뀌어도 수식을 다시 손볼 일이 줄어듭니다.

알아두면 좋은 활용

• 찾을 값이 여러 개의 조건을 만족해야 할 때도 배열 형태로 응용할 수 있습니다.
• MATCH의 마지막 인수를 1이나 -1로 바꾸면 근사값 찾기도 가능한데, 이건 오히려 실수하기 쉬워서 저는 거의 0(정확히 일치)만 씁니다.
• 값을 못 찾았을 때 #N/A가 뜨는 건 VLOOKUP과 동일하니, IFERROR와 같이 묶어서 쓰는 걸 추천합니다.

처음엔 수식이 두 개가 겹쳐 있어서 복잡해 보이지만, 몇 번 써보면 오히려 VLOOKUP보다 더 직관적으로 느껴지는 순간이 옵니다. 두 가지 조건으로 동시에 찾아야 하는 경우는 다음에 따로 다뤄볼게요.