VLOOKUP 왼쪽 값 참조 방법, 좌측 열 참조 방법
이전글에서 엑셀 함수의 꽃이라 불리는 VLOOKUP 함수를 배웠습니다.
VLOOKUP 은 찾을 값이 가장 좌측에 위치한 참조 범위의 열 방향 우측에 있는 값을 찾는 함수 입니다.
여기서 주목할 단어는 우측에 입니다. 찾을 값의 우측에 있는 참조 값만 가져올 수 있다는 것 입니다.
이게 무슨 문제가 될까? 하는 생각이 들 겁니다.
일반적인 경우에는 별 문제가 되지 않습니다.
하지만, 회사에서 사용할 때는 큰 문제가 될 수 있습니다.
기존의 양식을 변경할 수 없는 데이터 시트에서 좌측값을 참조해야 하는 경우가 있기 마련입니다.
이전글에서는 찾는 값의 위치를 출력하는 INDEX 함수와, 참조 범위에서 특정한 위치의 값을 출력해주는 MATCH 함수, 그리고 이 두 함수를 결합하여 INDEX MATCH 결합 함수로 사용하는 것에 대해 배웠습니다.
이번 글에서는 그대로 VLOOKUP 함수를 사용해서 찾을 값의 좌측에 있는 값을 가져올 수 있도록 하는 방법을 배워보겠습니다.
엑셀에서 값을 참조하기 위해 가장 많이 사용하는 함수는 바로 VLOOKUP 입니다.
하지만 VLOOKUP 함수에는 치명적인 단점이 있습니다.
바로 찾는 값의 우측에 있는 값만 참조할 수 있다는 것 입니다.
찾는 값의 우측에 있는 값만 참조할 수 있다면, 좌측에 있는 값은 어떻게 참조해야 할까요?
이전에 VLOOUP 함수를 대체해서 사용한 방법이 INDEX 함수와 MATCH 함수를 결합하여 사용하는 INDEX MATCH 함수 입니다.
그러나 VLOOKUP 함수에 익숙한 사용자들도 INDEX MATCH 함수를 결합하여 사용할 때 어려움을 겪습니다.
어려운 개념이 아니지만 함수 두 개를 결합하여 사용한다는 것 자체가 난이도 높은 문제로 느껴지기 때문입니다.
어렵게 느껴지는 INDEX MATCH 함수를 사용하지 않고 문제를 해결할 수 있는 팁들이 있습니다.
그대로 VLOOKUP 함수를 사용하는 방법에 대해 알아보겠습니다.
VLOOKUP 예제 변형을 통한 확인
아래의 예제는 이전에 VLOOKUP 함수를 배울 때 사용했던 것과 동일한 예제에 조금 변형을 했습니다.
VLOOKUP 함수를 INDEX MATCH 함수로 대체했을 때 사용한 예제와 동일하게 변형 했습니다.
즉, 찾는 값인 “이름” 컬럼이 찾으려는 값인 “생년월일” 값의 우측에 위치해서 기본적인 VLOOKUP 함수로는 해결할 수 없는 문제 입니다.
VLOOKUP 함수의 세 번째 인수 col_index_num 은 1보다 작은 값을 가질 수 없습니다.
찾는 값인 이름의 col_index_num 이 1이 되기 때문에 이보다 좌측에 있는 찾으려는 값인 생년월일의 col_index_num 은 표현할 방법이 없습니다.
즉, 좌측 열의 값을 참조할 수 없게 됩니다.
VLOOKUP 배열 참조를 통한 수식 변형
기본적인 VLOOKUP 함수 수식을 배열 참조를 이용해서 변형해 보겠습니다.
두 번째 인수인 참조 영역 table_array 를 배열 참조 {“김민준”,”2005.10.11″} 로 입력합니다.
위 배열 참조의 의미는 첫 번째 컬럼 첫 번째 열에 “김민준” 값이 입력되고, 두 번째 컬럼 두 번째 열에 “2005.10.11” 이 입력되었다는 뜻 입니다.
VLOOKUP 함수는 참조 영역의 첫 번째 컬럼에서 값을 찾아서 그와 같은 열에 있는 다른 컬럼의 값을 찾는 함수 입니다.
=VLOOKUP(E3,{“김민준”,”2005.10.11″},2,FALSE) 함수 수식은, E3 셀에 입력되어 있는 이름 김민준 을 배열 참조 {“김민준”,”2005.10.11″} 의 첫 번째 컬럼에서 찾아서 참조 범위의 두 번째 컬럼 값 2005.10.11 을 출력하라는 뜻 입니다.
이 수식이 이해가 되셨나요? 이해가 안되셨다면 그림과 설명을 다시 보시고 반드시 이해하시고 넘어가야 합니다.
한 번 이해를 하고 난 뒤에는 기계적으로 사용이 가능해 지므로, 반복해서 읽어보고 이해하시기 바랍니다.
VLOOKUP 배열 참조를 통한 변형 수식 완성
두 번째 인수인 참조 영역 table_array 에 입력된 배열 참조 {“김민준”,”2005.10.11″} 를 예제에서 필요로 하는 모든 참조 영역에 대해서 확장 입력 하겠습니다.
{“김민준”,”2005.10.11″;”이다혜”,”2007.4.15″;”유지태”,”1989.12.30″;”김민정”,”1997.5.21″;”박정아”,”2010.1.5″;”서경준”,”2002.7.23″}
위와 같이 입력하면 됩니다. 컴마(,) 기호로 열을 구분하고 세미콜론(;) 기호로 행을 구분하여 입력할 수 있습니다.
위와 같이 배열 참조를 입력하면 $B$3:$C$8 참조 영역의 모든 값이 배열 참조로 변환된 형태로 입력됩니다.
이 때 이름 값인 C열이 1열에 입력되고 생년월일 값인 B열이 2열로 입력되는 것이 핵심 입니다.
위와 같이 서로 자리를 바꿔서 입력이 되어야 이름(1열) 값으로 생년월일(2열) 값을 참조할 수 있게 되기 때문입니다.
E4 셀의 값을 다른 학생의 이름으로 변경해보면, 생년월일 값이 정상적으로 출력되고 있음을 확인할 수 있습니다.
즉, VLOOKUP 함수를 사용해서 왼쪽 값 참조(좌측 열 참조) 기능을 구현했습니다.
여기까지 이해가 되셨나요? 이해가 되지 않았다면 반복해서 읽어보시기 바랍니다.
하지만 문제가 있습니다.
VLOOKUP 함수의 두 번째 인수인 참조 영역 table_array 값을 매 번 배열 참조로 변형해서 입력해 둘 수는 없습니다.
데이터가 적은 경우는 가능할 수 있겠으나, 데이터가 많은 경우는 불가능에 가깝습니다.
그리고 참조 범위가 아닌 배열 참조 값을 직접 입력하는 것은 진정한 의미의 VLOOKUP 함수를 사용해서 왼쪽 값 참조를 구현하는 것 과는 거리가 있어 보입니다.
VLOOKUP 배열 참조를 참조 영역 값으로 적용하기
위의 그림을 여러 번 반복해서 보시기 바랍니다.
위의 그림에서 나타내고자 하는 핵심은 바로 아래와 같습니다.
배열 참조 {“김민준”,”2005.10.11″;”이다혜”,”2007.4.15″;”유지태”,”1989.12.30″;”김민정”,”1997.5.21″;”박정아”,”2010.1.5″;”서경준”,”2002.7.23″} 는
참조 영역 IF({1,0},$C$3:$C$8,$B$3:$B$8) 으로 대체할 수 있다.
이 부분은 이해가 잘 되지 않을 수 있습니다.
엑셀에 대한 이해가 깊은 사용자만 이해가 가능할 것 같습니다.
만약 이해가 잘 되지 않는다면, 위의 과정들을 거쳐 최종적으로 생성되는 함수 수식을 확인하고 외워서 사용하면서 점점 익숙해지다가 저절로 이해가 되는 것이 빠를 것 같습니다.
VLOOKUP 함수의 두 번째 인수 table_array 로 사용된 IF({1,0},$C$3:$C$8,$B$3:$B$8) IF 함수 수식이 배열 참조와 동일하게 작동한다는 것을 풀이해 보겠습니다.
엑셀 함수의 정확한 동작으로 완전하게 해설하는 것은 아직 어려울 것 같습니다.
제가 이해하고 사용하는 방식에 대해 설명해 보겠습니다.
VLOOKUP 배열 참조를 참조 영역 값으로 적용하기 풀이
IF 함수는 참과 거짓을 구분하는 함수 입니다.
IF 함수에 배열 참조 {1,0} 을 대입합니다. 참이면 이름 영역인 $C$3:$C$8 거짓이면 생년월일 영역인 $B$3:$B$8 이 출력됩니다.
IF 함수가 배열 참조에 대해 동작을 수행해서, 첫 번째 컬럼 값인 1에 대한 참, 거짓을 판단하고 결과 참(TRUE)을 돌려줍니다.
그러면 논리식이 참일 때 값인 $C$3:$C$8 값이 배열 참조의 첫 번째 컬럼들 즉, 1열에 차례로 들어갑니다.
{“김민준”;”이다혜”;”유지태”;”김민정”;”박정아”;”서경준”}
다음으로 IF 함수가 배열 참조에 대해 동작을 수행해서, 두 번째 컬럼 값인 0에 대한 참, 거짓을 판단하고 결과 거짓(FALSE)을 돌려줍니다.
그러면 논리식이 참일 때 값인 $B$3:$B$8 값이 배열 참조의 두 번째 컬럼들 즉, 2열에 차례로 들어갑니다.
{“김민준”,”2005.10.11″;”이다혜”,”2007.4.15″;”유지태”,”1989.12.30″;”김민정”,”1997.5.21″;”박정아”,”2010.1.5″;”서경준”,”2002.7.23″}
위의 배열 참조가 눈에 익숙한 형태인게 보이시죠?
그렇습니다. 위의 IF 함수 수식을 통해,
배열 참조 {“김민준”,”2005.10.11″;”이다혜”,”2007.4.15″;”유지태”,”1989.12.30″;”김민정”,”1997.5.21″;”박정아”,”2010.1.5″;”서경준”,”2002.7.23″} 는
참조 영역 IF({1,0},$C$3:$C$8,$B$3:$B$8) 으로 대체할 수 있다.
이 부분이 증명된 것 입니다.
위의 과정들을 통해 VLOOKUP 왼쪽 값 참조, 좌측 열 참조 기능을 구현해 봤습니다.
오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.
각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.
엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.