[엑셀] 특정 문자열에 해당하는 값 참조하기 VLOOKUP, MATCH

VLOOKUP 은 자주 등장하는 유용한 함수로, 다음과 같이 사용한다.

VLOOKUP(찾을 문자열, 참조할 데이터, 몇 번째 열의 셀을 참조할지 지정, 0)

마지막의 0은 '완전일치'를 뜻하는 옵션이다. FALSE로 입력해도 같은 의미이며

TRUE, 혹은 1을 입력할 경우 완전일치가 없으면 주어진 값을 넘지 않는 최대의 값을 반환하게 된다.

(마지막 인자를 생략하면 TRUE로 간주한다. TRUE에 대한 자세한 내용은 포스트 말미에서 확인하자.)

VLOOKUP 은 주로 위와 같이 쓰인다. 

=VLOOKUP(H2, A:F, 2, 0)

H2 셀에 있는 내용을 A열에서 찾아서 그로부터 2번째 위치한 열 (=B열) 의 값을 반환하라는 명령이다.

A:F는 참조할 셀을 찾을 범위인데, 위에서 2번째 열을 지정했으므로 사실 A:B 까지만 써 주어도 무방하다.

만일 1월 5일에 해당하는 합계를 구하고 싶다면?

같은 수식에 6번째 위치한 열의 값을 반환하도록 변경해 주었다.

(F열은 A열을 1번째로 볼 때 6번째 열이다.)

그리고 H2 셀에 쓰인 값을 1월 5일로 바꾸어 주었다.

시트의 열 구성이 크게 바뀌지 않는다면 이와 같이 적는 것으로 충분하다.

하지만 열이 삭제되거나 추가된다면 VLOOKUP은 제대로 동작하지 않는다.

예를 들어 '라'가 있는 E열을 삭제한다면

참조해야 할 'A로부터 6번째 열'에 아무 값도 존재하지 않으므로 결과가 나오지 않는다.

이렇게 열이 변화될 가능성이 있다면, '몇번째' 라고 지정하는 대신 '합계' 라는 글자를 찾아서 반환하도록 하는 편이 낫다.

그런 일을 담당하는 것이 MATCH 함수다.

=MATCH("가",1:1)

"가"라는 글자를 1행(1:1) 에서 찾으라는 의미이다.

2라는 결과는 "가"가 두 번째 열 (B열) 에 존재한다는 것을 의미한다.

그러므로 VLOOKUP을 다음과 같이 적으면 되겠다.

'몇 번째'를 지정할 위치에 '합계라는 글자가 있는 열'을 지정해 주었다.

1:1 대신 $1:$1 로 셀을 고정해 준 것은 다음과 같은 경우 때문이다.

1:1로 쓸 경우 아래로 수식을 복사하면 자동으로 2:2로 변경된다.

이는 2행에서 '합계'라는 글자를 찾으라는 명령이므로 오류를 출력하게 된다.

여기까지의 수식을 일반적으로 정리하면 다음과 같다.

=VLOOKUP(찾을 문자열이 있는 셀, 데이터 범위, MATCH("합계",$1:$1),0)

---

마지막으로 VLOOKUP의 마지막 인자를 TRUE(=1)로 할 경우 엑셀이 어떻게 생각하는지 보자.

찾을 내용과 정확히 일치하는 값이 데이터에 있을 경우에는 아래와 같이 TRUE도 FALSE와 마찬가지로 동작한다.

찾으려는 값과 정확히 일치하는 값이 데이터에 없을 경우 아래와 같이 근사값을 도출하는데

도출하는 원리는 다음과 같다.

이와 같이 16의 값을 반환하게 된다.