[Tip] vlookup함수 다중 조건으로 검색하기

   vlookup()함수 다중 조건으로 검색하기

vlookup()함수는 단일값으로 테이블에서 값을 찾는데 최적화된 테이블입니다. '=vlookup(찾을값, 테이블범위, 열순서, 옵션값)'으로 이루어져 있죠. 하지만 간혹 조회할 열이 유니크한 값들로 이루어져 있지 않고 다른 열과 조합했을 때 유니크해지는 경우가 있습니다. 이 때는 기존 vlookup함수만으론 검색이 불가능합니다. 추가 보조 열을 생성해서 해결해야 합니다.

 

 

위 예시에서 code가 b고 number가 3인 value를 조회하고자 합니다. vlookup() 함수의 찾을값에 b를 넣자니 b에는 각 number마다 다른 value들이 들어가 있어 원하는 값 검색이 불가능합니다. 이럴 때 함수만 써서는 불가능합니다. 유니크한 값들만 남길 수 있는 보조열을 만들어야 합니다. 중복된 행이 없을 때, 행에 있는 모든 값을 합쳐서 보조열을 만들어줍시다. 이 때, 텍스트를 합쳐줄 때는 열 개수가 그렇게 많지 않다면, 앰퍼샌드(&) 기호로 연결해서 만들어줍시다. concat()함수도 글자를 연결하는데 쓸 수 있는 함수지만, 시트 최적화를 위해선 함수보다는 수식들로 문제를 해결하는게 성능면에서 이득입니다.

 

 

code와 number를 합친 보조열을 만들었습니다. 이 테이블에서 중복된게 없는 가장 유니크한 열이죠. 이제 이 유니크한 열을 통해 vlookup() 함수로 원하는 value를 찾으시면 됩니다. =vlookup("b3", C:D, 2, 0) 이런 식으로 보조열을 찾을 값이 있는 열 앞에 넣어주고 함수를 작성하시면 원하는 값을 조회할 수 있습니다. 필요에 따라 보조열은 숨김처리 하시면 됩니다.

 

 

   나는 그래도 vlookup() 쓸꺼야!

위에서 함수만 써서 불가능하다고 했지만... 사실은 가능합니다! 하지만 권장하고 싶지 않습니다. 배열과 같은 어려운 개념들은 고급 사용자들에겐 익숙해서 쉽겠지만 업무상에서 시트를 공유할 때는 초급 사용자들도 쉽게 사용할 수 있는 함수를 쓰는게 서로 편합니다. 또한 유지보수면에서도 직관성이 떨어져 나중에 봤을 때 이해가 잘 안되는 경우도 발생하곤 합니다. 그래도 혹시 참고하실 분들을 위해 보조열을 사용하지 않고, 함수만 이용해 해결한 경우를 보여드립니다.

 

=vlookup("b3",{ArrayFormula(if(A2:A="","",A2:A & B2:B)),D2:D},2,0)

보기만 해도 어렵지 않나요? 저도 실무에선 보조열을 주로 활용합니다. 저렇게 복잡한 함수 생각하는 시간보다 더 빠르게 문제를 해결할 수 있기 때문이죠. 빠른 문제 해결을 위해서, 나중을 위해서, 유지보수를 위해서, 다른 사용자를 위해서 최대한 쉬운 방법으로 문제를 해결하는 것이 베스트입니다.

 

 

   filter()함수는 어떠세요?

filter함수는 테이블 또는 열에서 특정한 열들에게 조건을 걸어서 데이터를 출력하는 강력한 함수입니다. 기본 기능인 필터 기능을 함수로도 가능하게 해줍니다. '=filter(테이블 또는 단일열, 조건1, 조건2, 조건3....)' 이런 식으로 특정 범위에 대해 여러 조건으로 원하는 데이터를 뽑아낼 수 있는 함수입니다. 다만 단점으로는 굉장히 무거운 함수여서 수천, 수만셀에 끌어내려서 계산할 경우 랙이 발생할 수 있습니다. 또한, vlookup()과는 달리 arrayformula()를 통한 배열수식을 적용할 수 없다는 단점이 있습니다. 그래도 어찌되었든 결과를 뽑아내야한다 싶을 때 filter()함수도 고려해볼만합니다.

 

=filter(C:C,A:A="B",B:B=3)

우리가 원하는 value는 D열에 위치해서 찾을 범위를 D열만 정했습니다. 그리고 code 열에선 "b"를 찾을 수 있도록 A:A="b", number열에서는 3을 찾을 수 있도록 B:B=3 조건을 작성했습니다. 이 때, 주의하실 점으로는 찾을 범위랑 조건의 행을 똑같이 작성해주세요. 만약 찾을 범위가 A1:A10인데 조건은 B2:B10에 대해서만 작성하면 에러가 발생하여 계산이 불가능합니다. 함수를 작성하시면 위 그림처럼 의도했던 결과가 계산되는걸 확인하실 수 있습니다.

 

filter 함수는 단일값이 아니라 배열을 리턴하는 함수입니다. 여기선 code "b", number 3인 value가 하나밖에 없었지만, 만약 code와 number가 중복된 value가 더 있다면 그것도 같이 가져올겁니다. 원래 이런 용도지만요... 의도한 단일 값 계산을 위해선 유니크한 행들만 필터 되도록 최대한 많은 조건식을 넣어주는게 좋습니다. 이렇게 유니크 한 값을 최대한 안정적으로 가져온다는 점에선 vlookup()보다 좋다고 할 수 있습니다. vlookup은 조건이 중복된 값이 있을 경우 가장 첫번째 것만 가져오는 한계점이 있기 때문이죠.