이전글에서 VLOOKUP 함수를 사용해서 찾는 값의 왼쪽(좌측)에 있는 찾으려는 값을 참조하는 방법을 배웠습니다.
이 방법을 이해하고 편하게 적용할 수 있는 사용자는 문제가 없습니다.
이 방법을 이해하려고 노력했으나 잘 이해가 되지 않아 사용이 불가한 사용자들에게 도움이 되는 방법을 소개해 드리겠습니다.
VLOOKUP 안되는 경우
이전글에서 엑셀 함수의 꽃이라 불리는 VLOOKUP 함수를 배웠습니다.
VLOOKUP 은 찾을 값이 가장 좌측에 위치한 참조 범위의 열 방향 우측에 있는 값을 찾는 함수 입니다.
여기서 주목할 단어는 우측에 입니다. 찾을 값의 우측에 있는 참조 값만 가져올 수 있다는 것 입니다.
이게 무슨 문제가 될까? 하는 생각이 들 겁니다.
일반적인 경우에는 별 문제가 되지 않습니다.
하지만, 회사에서 사용할 때는 큰 문제가 될 수 있습니다.
기존의 양식을 변경할 수 없는 데이터 시트에서 좌측값을 참조해야 하는 경우가 있기 마련입니다.
이전글에서는 찾는 값의 위치를 출력하는 INDEX 함수와, 참조 범위에서 특정한 위치의 값을 출력해주는 MATCH 함수, 그리고 이 두 함수를 결합하여 INDEX MATCH 결합 함수로 사용하는 것에 대해 배웠습니다.
이번 글에서는 VLOOKUP 안되는 경우 약간의 조작을 통해 VLOOKUP 함수를 사용해서 찾을 값의 좌측에 있는 값을 가져올 수 있도록 하는 방법을 배워보겠습니다.
엑셀에서 값을 참조하기 위해 가장 많이 사용하는 함수는 바로 VLOOKUP 입니다.
하지만 VLOOKUP 함수에는 치명적인 단점이 있습니다.
바로 찾는 값의 우측에 있는 값만 참조할 수 있다는 것 입니다.
찾는 값의 우측에 있는 값만 참조할 수 있다면, 좌측에 있는 값은 어떻게 참조해야 할까요?
이전에 VLOOUP 함수를 대체해서 사용한 방법이 INDEX 함수와 MATCH 함수를 결합하여 사용하는 INDEX MATCH 함수 입니다.
그러나 VLOOKUP 함수에 익숙한 사용자들도 INDEX MATCH 함수를 결합하여 사용할 때 어려움을 겪습니다.
어려운 개념이 아니지만 함수 두 개를 결합하여 사용한다는 것 자체가 난이도 높은 문제로 느껴지기 때문입니다.
어렵게 느껴지는 INDEX MATCH 함수를 사용하지 않고 문제를 해결할 수 있는 팁들이 있습니다.
그 중 하나는 이전글에서 이미 배워봤습니다.
이번에는이 방법이 어려우신 분들을 위해, 약간의 조작을 통해 VLOOKUP 함수를 사용하는 방법에 대해 알아보겠습니다.
VLOOKUP 왼쪽 값 참조를 위한 참조 테이블 조작
아래의 예제는 이전에 VLOOKUP 함수를 배울 때 사용했던 것과 동일한 예제에 조금 변형을 했습니다.
VLOOKUP 함수는 찾는 값의 col_index_num 이 1이 되어야 합니다.
즉, 찾는 값이 참조 영역의 가장 왼쪽에 위치해야 합니다.
위의 그림에서 찾는 값 “이름“은 찾으려는 값 “생년월일“보다 우측에 있습니다.
그렇게 때문에 찾으려는 값 “생년월일”에 해당하는 col_index_num 값을 지정할 수 없게 되고, VLOOKUP 함수를 사용할 수 없습니다.
VLOOKUP 함수의 세 번째 인수 col_index_num 은 1보다 작은 값을 가질 수 없기 때문입니다.
그래서 참조 영역 테이블에 약간의 조작을 해서 VLOOKUP 함수를 사용할 수 있도록 해보겠습니다.
VLOOKUP 함수의 찾는 값 컬럼에 해당하는 열을 전체 선택하여 복사 합니다.
복사한 열을 참조 영역 테이블의 가장 좌측 열 타이틀(B)을 을 선택한 후 “복사한 셀 삽입“을 합니다.
그러면 B열에 이름 열이 추가로 생성됩니다. 생년월일은 C열로 이동하고 이름은 D열로 이동합니다.
즉, 참조 영역 테이블 가장 좌측에 열 하나가 추가되어 모든 셀이 한 열 우측으로 이동됩니다.
이제 VLOOKUP 함수를 사용하여 이름 값으로 생년월일 값을 참조할 수 있는 상태가 되었습니다.
VLOOKUP 함수 사용하여 값 참조
VLOOKUP 의 기본적인 함수 수식을 사용하여 값을 참조해 보겠습니다.
VLOOKUP 함수의 두 번째 인수인 참조 영역 설정을 주의깊게 보시기 바랍니다.
복사하여 추가한 B열 이름의 범위를 포함하여 참조 영역을 설정해줘야 합니다.
G3 셀에 함수 수식 =VLOOKUP(F3,$B$3:$D$8,2,FALSE) 를 입력합니다.
그러면 결과값으로 김민준 학생의 생년월일 “2005.10.11” 값이 출력됩니다.
G3 셀의 함수 수식을 복사하여 G4 셀에 적용하면, 이다혜 학생의 생년월일 “2007.4.15” 값이 출력됩니다.
두 값 모두 정확하게 출력되었습니다.
이제 화면상에 보여질 필요가 없는 B열 이름을 셀 숨기기 기능을 사용하여 숨기겠습니다.
어떻습니까? 이전의 방법들과 동일한 결과를 얻은 것이 보이시나요?
엄밀히 말하면 동일한 결과는 아닙니다.
B열이 숨겨진 상태로 되어 있고, 열이 하나씩 우측으로 이동했기 때문입니다.
위의 작업을 통해 화면에 보여지는 결과는 다른 방법을 사용했을 때와 마찬가지로 동일하게 출력됩니다.
생년월일과 이름의 출력 순서를 변경할 수는 없는 상태에서 이름으로 생년월일 값을 참조하기 위해서 사용할 수 있는 간단한 트릭 입니다.
가상의 찾는 값을 B열 이름 으로 추가해준 후 눈에 보이지 않도록 해주는 것 입니다.
만약 새로운 이름과 생년월일이 추가된다면, 숨겨져 있는 B열의 이름 값도 동일한 값으로 복사되어야 합니다.
사용상에 약간의 제약이 있으나, 간편함으로 따지면 가장 간단한 방법이 될 수 있습니다.
VLOOKUP 함수를 사용해서 찾을 값 좌측의 찾으려는 값을 참조하는 다양한 방법을 배웠습니다.
모든 경우에 가장 좋은 방법은 어떤 방법이다 라고 특정하기는 어렵습니다.
개개인의 엑셀 실력과 업무의 특성에 따라 최적의 방법이 결정될 것 입니다.
다양한 방법을 익혀두고 상황에 맞는 최적의 방법으로 문제를 해결하시기 바랍니다.
각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.
엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.