3~5행에 입력된 조건에 맞는 값은 하나씩 밖에 없으므로 정상적으로 모델명이 반환 되었다.
그렇다면 오류가 나는 부분에 대해 한번 살펴보자...
6~7행의 경우엔 오류가 나있다.
이유를 살펴보면 두개의 조건이 모두 공란일 경우 '품목DB'시트를 보면 그에 매칭되는 값 역시 공란인데 이를 만족하는 값이 여러개 있어 그에 맞는 모든 값을 반환시키게 된것이다.
그런데 바로 그 다음행에 FILTER함수가 기입되어 있으니 '분할범위가 비어있지 않아 오류가 발생'하게 된 것이다.
또 다른 오류는 오피스365 버전에서도 아직 그대로인 엑셀의
단점으로...
예제에서는 FILTER함수를 8행까지 드래그해서 반영했으므로, 마지막 8행 이후의 셀은 비어있어 공란 2개의 조건을 만족하는 값 '0'을로 모두 반환한 것을 볼 수 있다.
문제는 '공란'을 반영해야 함에도 '0'을 반환했다는 것이다.
이것은 FILTER함수의 문제가 아니라 엑셀에서는 기본적으로 공란을 '0'으로 반환하기 때문이다.
이런 이유로 공란과 0을 정확히 구분하기 위해 IF함수를 추가하여 강제로 구분시켜 준다.
이게 무슨 문제라도? 라고 생각한다면…
데이터가 비어있다는 것과 0이라는 것은 전혀 다른 상황으로 거짓값을 반환해 준다는 뜻이다.
반면에 구글 스프레드시트의 경우엔 '공란'을 그대로 반환해 준다.
(이 부분이 엑셀과 구글 스프레드시트의 차이점 중 하나이다.)
동일한
예제에 FILTER함수를 적용한 아래 그림을 비교해 보자.
구글 스프레드시트의 경우 공란을 반영해 준다.
이제 포스팅의 주제인 VLOOKUP으로 돌아와 2개 이상의 중복조건을 검색하는 방법을 살펴보자.
앞에서 언급했듯이 간단하다.
2개 또는 그 이상 여러개라는 조건자체를 단일조건으로 만들어 진행한다.
단일조건은 아래와 같이 열을 하나 추가하여 두개의 조건을 하나로 합친다.
=C3 & "/" &
D3
=IF(C3="","",C3 & "/" & D3)
예제에서는 &와 " "를 이용하여 문자열을 만들었다. (&와 " "를 이용한 문자열 조합은 네이버포스트 참조)
구분자는 되도록 잘 사용하지 않는 기호를 쓰는게 좋으며 예제는 / 를 사용하였다.
추가로 조건이 공란인
경우에도 / 를 반환하게 되므로 IF함수를 추가하여 공란을 반환하도록 하였다.
( * 구지 두개의 조건을 구분자(/)를 사용하여 연결하는 이유는 합쳐진 조건을 다시 두개로 분리할때 편하기 때문이다. 예들 들자면 이전 QR코드 생성에 있어 여러개의 정보를 구분자와 함께 QR코드 정보로 지정해 놓으면, 반대로 QR코드에서 구분자를 기준으로 손쉽게 특정 정보만 뽑아낼 수 있다. )
이 경우 TEXTJOIN함수를 사용하여 구성하면 아래의 그림처럼 공란일 조건도 같이 처리할 수 있다.
항상 하는 말이지만 어떤 함수를 사용할지는 개인 선택사항이다.
TEXTJOIN 함수를 사용
=TEXTJOIN("/",1,C3:D3)
TEXTJOIN 함수의 두번째 인수를 1(TRUE)로 입력하면, 공란일 경우를 포함시키지 않는다.
( TEXTJOIN 함수는 블로그 포스팅 참조.)
구글 스프레드시트의 경우엔...
품목DB가 계속 추가될것을 고려하여 ARRAYFORMULA 함수를 걸어두면 좋다.
ARRAYFORMULA 함수 적용
=ARRAYFORMULA(IF(C3:C="","",C3:C & "/" & D3:D))
다음으로 VLOOKUP 함수를 적용할 두번째 Transaction 시트에도 동일하게 열 추가를 하고 두개의 조건을 하나로 합쳐준다.
VLOOKUP 함수를 적용할 시트에도 열을 추가하여 두개의 조건을 하나로 합친다.
이제.. 새로 추가된 열에 대해 VLOOKUP 함수를 적용해주기만 하면 된다.
문자열을 조합한 열에 대해 VLOOKUP을 걸어준다.
=VLOOKUP(A3,'품목DB'!B:E,4,0)
예제에서는 품목DB 시트의 데이터가 계속 추가되는 것을 고려하여 B~E열 전체를 검색범위로 잡았다.
조건이 2개가 아니라 그 이상이 되더라도 위와 같이 하나의 문자열로 조합하여 적용하면 된다.
마지막으로 구글 스프레드시트에서는...
역시나 ARRAYFORMULA 함수를 사용하여 전체행 적용을 걸어두는게
좋다.
=ARRAYFORMULA(VLOOKUP(A3:A,'품목DB'!B:E,4,0))
ARRAYFORMULA 함수 적용
만약, 해당 파일을 여러사람이 공동작업 한다면 단일조건검색으로 만든 열에 대해 시트보호를 걸어두고 숨김처리를 해 두는게 좋다.
다음 포스팅에서 소개할 VLOOKUP 함수 활용은...
IF,IFS함수 대신 값의 범위조건에 맞는 데이터를 반환하는 방법에 대한 이야기다.
👉 해당내용 관련 네이버TV
"구독!" : https://tv.naver.com/v/19713829
