엑셀 LOOKUP 함수 배열기초와 진실 혹은 거짓

 

LOOKUP 함수란?

찾는 대상에 대응하는 열 혹은 행의 값을 추출 해오는 함수로 직접적인 추출함수이나 배열함수로 많이 활용되고 있는 함수이다. 변종으로 vlookup과 hlookup 최근에는 xlookup 까지 변종이 확대되고 있으며, 일반적으로 가장 많은 활용도를 보이는 함수중 하나이다.

이중에서도 lookup은 다양한 룩업함수의 기본이 되는 초기모델이고 사용방법 또한 까다로워 많이 사용되지 않기도 한다.

함수적용 기본

=LOOKUP(찾을값,찾을범위,추출범위) 기본형태는 나는 "이것"을 찾을테니 "여기"에서 똑같은 값이 있다면 "이곳"에서 대응되는 값을 보여줘라 라는 함수이다. 그러나 배열함수에서 많이 사용하게 되며 배열함수에서는 LOOKUP(찾을값,조건,추출범위)로 사용 되어 진다.

 

예제

조건 두가지를 만족할 때 결과값을 찾아오는 LOOKUP 함수에 대해 알아보자.

 

예제에서 조건1은 땅/바다/산 이고 조건2는 생산품명이다. 이 두가지 조건을 매칭하여 음식 이름을 찾아올 것이다. 사용된 함수 구조는 "'=LOOKUP(1,0/((A7:A14=B2)*(B7:B14=C2)),C7:C14)"이며, lookup 단일 함수로 완성했다. 특징으로는 '0으로 나눗셈을 한 부분'과 '조건과 조건간에 곱셈으로 연결'하고 있다는 점만 유념하면 좋을 것 같다.

 

문제의 풀이

입력구조 기본에서는 찾는값/찾을곳/꺼낼곳 정도로 간단하게 표현했으며, 배열수식인 경우 찾는값/찾는법/꺼낼곳 으로 변형이 있다고 앞서 언급이 있었고 여기에서는 후자의 방법으로 함수식이 작성 되어 있다.

찾는값 = 1
찾는법 = 0/((A7:A14=B2)*(B7:B14=C2))
꺼낼곳 = C7:C14

구조 자체는 아주 단순하고 깔끔하다. 여기서 이해해야 할 부분은 찾는값이 1 이라는 점과 찾는 방법의 0/ 부분이 가장 주된 요소이다.

☞ 찾는값이 1인 이유

배열수식에서는 열과 열 혹은 행과 행 대응 기준을 0 혹은 1 형태의 숫자로 얻어내는 구현되는 것이 일반적이다. 그리고 조건과 조건에 곱셈으로 연결된 것으로 봐서는 조건에 대한 결과또한 0 혹은 1로 나타낼 것임을 잘 알 수 있다. 좀 더 쉽게 말하면 모든 조건이 1일 때 그것을 추출하겠다 라는 형태이다.

다시말하자면 내가 찾고자 하는 단어는 "산+버섯" 이다. 역으로 표현하면 산도 1 버섯도 1 일때 그곳을 정답으로 인식하고자 한다.. 라는 의미이다.

 

조건의 추출

조건1에서는 '산'인지 아닌지를 판단해 일치하는 경우 TRUE를 내어준다. 조건2에서는 '버섯'인지 아닌지를 판단해 일치하는 경우 TRUE를 내어준다. 그리고 조건간에서는 각 결과값을 곱셈으로 받아주고 있다.

기초지식으로 TRUE=1, FALSE=0 을 의미하기 때문에 곱셈 결과값을 보면 11번 행의 값에서 1의 결과를 얻는 모습을 볼 수있다.

여기까지 간단하게 알아본 lookup 함수의 기초 활용법 이다.

 

---

죄송하게도 위에까지는 보통 LOOKUP 함수를 뒤지면 찾아볼 수 있는 기본 형태를 가지고 학습을 해봤다 하지만 LOOKUP 함수는 불완전 함수로 일부 기능이 없이 돌아가는 몇 안되는 함수중 하나이다. 오래된 함수이고 그만큼 빈틈도 활용성도 많다.

여기서 궁금증을 가져야 하는 요소는 '찾는 1값의 의미'와 '나누는 0의 의미'일 것이다.

 

"0/"의 의미

오늘 학습 중 가장 이해하기 어려운 부분이다. 개념이 살짝 뒤틀리는 순간이니 정신 똑바로 차리고 학습해보자.
얻어진 결과값을 모두 0으로 나눗셈을 하면 결과는 나눗셈이 되서 0이 나오거나 혹은 오류값 밖에 나오지 않는다.

 

결과론적으로 보면 lookup은 배열수식에서 숫자나 문자를 찾아내는 함수가 아닌게 된다. 바로 0이냐 아니냐를 따져 답을 가져오는 모습을 보여주고 있다. 어떻게 보면 정상이냐 비정상이냐를 따지는 것 같지만 몇가지 테스트를 해보면 답은 금방 얻을 수 있다. 보통의 경우 오류값을 포함하면 구동이 안되는게 일반적이나 구동 되는거 봐서는 LOOKUP은 일단 분명 오류값도 이해하고 있다.

무슨말인지 간단한 테스트를 통해서 확인 해보자

 

 

중복값이 있는 경우 더 아래쪽 결과값을 끌어온다. 이런 결과가 나오는 이유는 그냥 LOOKUP 함수의 특징이다. 별다른 의미나 뜻이 있는 것이 아니라 vlookup 같은 경우 중복시 위부터 끌어온다면 lookup은 아래부터 끌고 오는것이 본래의 특징일 뿐이다.

여하튼 여기서 확인 할 수 있는 결과는 오류는 분명 걸르고 있다는 것이다. 여기서 한번더 생각해볼 부분은 LOOKUP은 과연 참값을 바라보는 것인가? 혹은 숫자만 바라보는 것인가? 아니면 반대로 오류만 무시하는 것인가? 를 생각해볼 수 있다.

이 가설을 증명할 수 있는 녀석이 바로 맨앞 '찾는값 1'이다. 오늘 적용한 함수식 '=LOOKUP(1,0/((A7:A14=B2)*(B7:B14=C2)),C7:C14)' 중에서 찾는값 1을 건드려보면 모든 답이 풀린다.

 

 

찾는값을 100을 입력해도 결과값이 변하지 않는다.

 

 

그리고 어떤 조건을 입력해도 결과는 제대로 나온다. 배열수식에서는 찾는 값이 무시가 되고 있다...그래서!!!!!!!!

 

 

아무것도 입력하지 않았다... 그런데도 답이 제대로 나온다.

결론은 1값을 찾는게 아닌데도 불구하고 많은 지식인들은 1값을 찾는 것 처럼 가르쳐 주었다...
LOOKUP은 배열 구조인 경우 숫자를 찾아내는 능력은 없는 것 같다. 

이제 마지막 남은 테스트는 그럼 숫자를 바라보는 것인가 아닌가를 가늠하는 것이다. 이번에는 0/ 요 부분을 다른 숫자를 넣어볼 것이다.

 

 

버섯튀김이 나와야 정상일텐데 먹통이된다.... 안돌아간다. 1/의 경우 찾는값을 1이 아닌 다른거로 바꿔도 먹통이 된다. 

그래서 결론은 조건에는 무조건 0으로 나눠주어야 하며, '찾는값'은 무의미하다!
LOOKUP은 배열 수식에서 조건값을 받아들여 0값만 찾아서 맨 마지막 결과값을 끌어오는 모습을 보였다. 오류냐 아니냐도 아니었고 숫자냐 아니냐도 아니었다. 무조건 조건값이 0이 되어야 결과를 끌어왔다는 것.

=LOOKUP(,0/((A7:A14=B2)*(B7:B14=C2)),C7:C14)

 

오늘의 결론

LOOKUP 함수를 배열 구조로 환산하는 경우 조건은 0으로 나누어주는 경우 찾는 값은 의미가 없다는 것, LOOKUP이 바라보는 결과 값은 결국 "0" 이라는 것이 확인 됐고 찾는 값이 의미가 없어진 이유는 LOOKUP 함수는 아래에서부터 찾아오기 때문에 정답이 100개가 있어도 100번째만 가지고 오기 때문에 구지 찾는 값을 알 필요도 없다는 것이다. 찾는 갓은 이미 조건에서 드라나기 때문...

오늘 lookup을 공부해본 이유는 "(1,1/"  로 입력해서 구동을 해보니 구동이 되어야 하는데 오류가 나는 경우가 확인 되었기 때문에 안정적인 구조 구현을 위해 공부를 해봤고 조건을 0에다 나누는 것이 가장 안정적이었습니다. lookup 함수 오늘만 3번째 학습 중인데도 상당히 어렵네요... 왜 index가 인기 있는지 이제야 알 것 같네요.. 넘 이해하기 어려운 함수였습니다.

lookup 다중조건 기초.xlsx

0.02MB

 

감사합니다.