HLOOKUP, LOOKUP 함수 파헤치기

HLOOKUP, LOOKUP 함수 파헤치기

 

HLOOKUP 함수는 VLOOKUP 함수와 사용법은 동일하나 매칭 값과 매칭 값을 이용하여 찾으려는 반환 값이 표시된 테이블의 구조가 다릅니다.

VLOOKUP 한수와 HLOOKUP 함수 비교

VLOOKUP 함수는 매칭 값이 테이블의 가장 좌측 열에 표시되어 있으며, 이 매칭 값과 비교하여 우측 열에 있는 값을 반환하게 됩니다.

HLOOKUP 함수는 매칭 값이 테이블의 상단에 위치해 있으며, 매칭 값과 비교한 후 함수의 인수로 지정한 행 번호에 맞는 값을 반환하게 됩니다.

 

쉽게 VLOOKUP 함수를 시계방향으로 90도 돌려놓은 모습이라 생각하면 됩니다.

 

함수의 사용법은 크게 다르지 않으므로 간단한 예제를 통해 알아보도록 하겠습니다.

 

 

1. HLOOKUP 함수 예제

예제

VLOOKUP 함수 예제와 비슷한 구성이며, 주어진 테이블(표)에 따라 결과를 표시하는 표가 두 가지로 구분되어 있습니다.

 

매칭해야 되는 고유 번호가 데이터가 입력된 표의 상단에 위치해 있으므로 HLOOKUP 함수를 사용해야 됩니다.

HLOOKUP 함수를 이용해 구해보면 수식은 HLOOKUP(고유번호,데이터 범위,반환 값의 위치,0)로 작성할 수 있습니다.

 

이를 통해 1번 표의 결과를 구해보면 아래와 같이 나타낼 수 있습니다.

예제 풀이(결과1)

결과표에서 고유 번호는 'B~D'열의 '7행'에 표시되어 있으므로 수식을 우측으로 이동시키기 위해 '7'행만 고정하고 'B열'은 고정하지 않습니다.

이후 데이터가 입력된 표의 'B2'셀부터 'I4'셀까지 범위를 잡은 후 수식을 이동해도 범위가 이동하지 않도록 행과 열 모두 고정시켜 줍니다.

그리고 이름값은 데이터의 매칭 하는 고유 번호를 기준으로 두 번째 행에 위치해 있으므로 가져오는 데이터의 행 인수에 숫자 '2'를 입력시키면 완료됩니다.

구매일은 행 인수 번호를 '3'으로 입력하면 완료됩니다.

 

표의 배치가 다른 2번 결과표의 수식은 아래와 같이 작성할 수 있습니다.

예제 풀이(결과2)

결과 표의 형태는 VLOOKUP 함수를 다룰 때 봤던 형태이지만 매칭해야 되는 값과 그 값에 대응하는 결과 값을 반환하는 데이터의 형태가 고유번호가 상단에 위치한 형태이므로 HLOOKUP 함수를 사용해야 되는 것을 확인할 수 있습니다.

 

전체적인 수식 작성 방법은 비슷하나 2번 결과표는 고유 번호가 'A'열에 입력되어 있으므로 고유 번호에 따른 '이름'이나 '구매일' 정보를 가져올 때 사용하는 수식은 작성 후 아래로 드래그하게 됩니다.

이때 열은 변하지 않으나 행은 고유 번호에 따라 '8', '9', '10'으로 변하므로 행은 고정하지 않은 채 수식을 작성하면 됩니다.

 

※ 결과를 구하는 표의 형태가 아닌 결과를 가져와야 되는 원래 데이터의 형태에 따라 사용해야 될 함수를 선택하면 됩니다.

 

 

2. LOOKUP 함수

 

VLOOKUP 함수나 HLOOKUP 함수는 많이 들어봤으나 LOOKUP 함수는 의외로 많은 분들이 잘 모르는 함수입니다.

사용법만 잘 익히게 되면 유용하게 사용이 가능하며, VLOOKUP이나 HLOOKUP 함수는 기준 조건이 하나일 경우에만 사용이 가능한 반면 LOOKUP 함수는 여러 조건에서도 활용이 가능한 장점이 있습니다.

 

함수의 구조는 LOOKUP(매칭 값,매칭 값에 대응하는 범위,결과 범위)의 형태입니다. 구조는 단순하지만 사용법은 조금 까다로운데 이유는 정확히 원하는 결과물을 얻기 위해서는 다소 복잡한 과정이 필요하기 때문입니다.

 

매칭해야 되는 기준값이 테이블의 중간 혹은 우측에 있는 경우처럼 VLOOKUP 함수로 구할 수 없는 경우 INDEX, MATCH 함수를 활용하기도 합니다. 하지만 LOOKUP 함수를 익혀두면 INDEX, MATCH 함수를 사용하지 않고도 해결이 가능하며, 두 개 이상의 조건을 만족하는 결과 값을 구할 수도 있으므로 유용하게 쓸 수 있습니다.

 

LOOKUP 함수의 수식은 공통적으로 LOOKUP(1,1/(조건 식),결과 범위) 형태로 사용하며, 조건식이 여러 가지일 경우 LOOKUP(1,1/((조건식 1)*(조건식 2)*(조건식 3)...),결과 범위)의 형태로 사용할 예정입니다.

 

위의 사용 법은 매칭 값을 '1'로 지정해놓고 조건식에서 찾고자 하는 대응 값이 'TRUE'가 나오도록 하고 조건에 맞지 않는 경우 'FALSE'가 나오도록 합니다.

이 조건식에서 나온 결과는 모두 숫자 1을 나누는 분모가 되며, FALSE는 0을 뜻하고 TRUE는 1을 뜻하므로 조건에 맞지 않는 값들은 '1/0'과 같아 '#DIV/0!'라는 오류로 표시되고 조건에 맞는 값은 '1/1'이므로 결과가 '1'이 나오게 되어 LOOKUP 함수의 첫 번째 인수인 매칭 값 1과 맞아 찾으려는 값으로 인식하게 됩니다.

 

예제를 통해 대략적으로 살펴보도록 하겠습니다.

 

 

3. LOOKUP 함수 예제

예제

위 예제에서 주어진 표에 이름은 중간에 표시되어 있으나 최종 결과에는 해당 이름에 따른 나이와 거주지를 구하는 문제 형태입니다.

 

거주지의 경우 VLOOKUP 함수를 통해 구할 수도 있으나 현재 형태만으로 이름에 따른 나이를 불러오는 것은 불가능합니다.
가장 좌측에 별도의 열을 만들어 이름을 붙여 넣은 후 VLOOKUP 함수를 사용해도 되지만 LOOKUP 함수를 통해 해결할 수도 있습니다.

 

방법은 아래와 같습니다.

예제 풀이

앞서 설명한 수식에서 LOOKUP의 조건식 부분에 A10에 입력된 이름과 B3~B7셀까지 입력된 이름이 동일한지 여부를 수식 'A10=B3:B7'로 작성하여 'TRUE' 또는 'FALSE'로 조건에 대한 값이 나오도록 합니다.

이에 따라 B 10열의 값을 통해 비교해보면 A10의 이름은 'B'이며, 문제에서 제시된 표의 이름이 기재된 B3~B7의 값은 각각 A, B, C, D, E입니다.

조건식을 그대로 확인해보면 A10은 'B'인데 B3는 'A'이므로 같지 않아 FALSE, B4는 'B'이므로 값이 같아 TRUE, 이런 식으로 나머지 조건들도 FALSE가 나오게 됩니다.

 

따라서 조건식에서 TRUE가 나오는 위치는 범위에서 두 번째 행인 '2'이며, 이 두 번째 행을 결과 범위에서 그대로 가져오면, 나이는 '26', 거주지는 '경기'가 되므로 해당 값을 반환하게 됩니다.

 

사용법에 대해 다소 이해가 필요하긴 하나 추후에 LOOKUP 함수는 더욱 자세히 다루면서 두 가지 이상의 조건에서의 활용법도 알아보도록 하겠습니다.