엑셀함수 LOOKUP – 행 또는 열의 같은 위치에 있는 값 찾기

공개됨 글쓴이 Admin댓글 한 개

LOOKUP함수는 이름 그대로 뭔가를 찾는 함수이다. 비슷한 함수로 VLOOKUP, HLOOKUP이 있다. 이들 함수와 사용법도 비슷하고 기능도 비슷하지만 어떤 범위에 포함되는 값을 찾을 때는 LOOKUP함수로 해결할 수 있는 경우도 있으니 익혀두면 많은 도움이 된다.

1. LOOKUP – 벡터형

구문(Syntax)
LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP의 벡터형은 한 개의 행이나 한 개의 열로 이루어진 범위(벡터)에서 값을 찾고, 한 개의 행이나 한 개의 열로 이루어진 두 번째 범위의 같은 위치에서 값을 반환한다.

인수 :
– lookup_value : 찾는 값
– lookup_vector : 찾는 범위, 하나의 행이나 열로 된 범위, lookup_vector안에 있는 값은 0,1,2…. A,B,C의 형태로 오름차순으로 정렬되어 있어야 한다. 대소문자는 구별하지 않는다.
– result_vector : 하나의 행이나 열로 된 범위. lookup_vector와 같은 행 또는 열에 있는 값이 반환된다.

LOOKUP 벡터형 이해하기 – 일치하는 값 찾기

LOOKUP함수를 이용하기 위해서는 값을 찾는 범위가 정렬되어 있어야 한다.
값을 찾을 때 정확히 찾는 값이 없으면 근사값을 찾는다. 이 성질을 이용하여 성적별 학점을 구하는데 사용할 수 있다.

다음 그림과 같이 점수별로 성적등급을 구하는 표가 있을 때 점수에 정확히 일치하는 성적등급을 구해보자.

F5셀에 다음 수식을 입력한다.

=LOOKUP(90,B5:B13,C5:C13)

90점에 해당하는 “A0″가 구해진다.

“A0″가 구해지는 과정을 그림으로 설명하면 다음과 같다.

  • 취득점수가 있는 범위 B5:B13 (lookup_vector)에서
  • 90점을 찾아서(lookup_value)
  • 같은 행에 있는 성적등급 범위 C5:C13(result_vector)에서
  • “A0″를 찾는다

 

LOOKUP 벡터형 이해하기 – 근사 값 찾기

다음 그림과 같이 점수별로 성적등급을 구하는 표가 있을 때 점수에 해당하는 성적등급을 구해보자.

F17셀에 다음 수식을 입력한다.

=LOOKUP(83,B17:B25,C17:C25)

83점에 해당하는 “B0″가 구해진다.

“B0″가 구해지는 과정을 그림으로 설명하면 다음과 같다.

  • 취득점수가 있는 범위 B17:B25 (lookup_vector)에서
  • 83점을 찾는데(lookup_value), 83점이 취득점수 범위에 없으므로 83점보다 작은 값중에 가장 가까운 값, 즉 근사값 80점을 찾는다
  • 같은 행에 있는 성적등급 범위 C17:C25(result_vector)에서
  • “B0″를 찾는다

위의 예처럼 어떤 행의 같은 위치에 있는 값을 찾기 위해 LOOKUP 벡터형을 사용할 수 있지만

INDEX함수와 MATCH함수를 이용해서 다음과 같이 같은 결과를 구할 수도 있다.
=INDEX(C5:C13,MATCH(83,B5:B13,1),1) 

INDEX,MATCH함수에 대해서는 다음 링크를 클릭!

[ INDEX, MATCH를 이용한 다중조건으로 값찾기 ]

 

LOOKUP 벡터형 응용하기 – 범위에 있는 값 찾기

다음 그림과 같은 표가 있을 때 특정 값이 어떤 범위에 존재(시작값과 끝값 사이이 존재)하는 지 찾아 보자.

위의 표를 그림으로 표현하면 다음과 같다.

예를 들어 650이라는 값은 No. A002 범위에 있는 값인데 이 값을 다음 수식으로 구할 수 있다.

=LOOKUP(2,1/((650>=C5:C7)*(650<=D5:D7)),B5:B7)

수식이 무지 복잡하다. 배열수식을 이해해야 이해가 되는 수식이다.

인내를 갖고 설명을 따라가 보자.

다음 그림처럼

  • 수식이 입력된 셀을 선택한 후
  • 수식입력줄에서 “(650>=C5:C7)”을 마우스로 선택하고
  • F9 키를 누르면 {TRUE;TRUE;FALSE}로 바뀐다.

{TRUE;TRUE;FALSE}와 같이 중괄호로 표시된 부분이 배열인데 다음 그림과 같이 표현할 수 있다.

C5:C7영역에서 650보다 작거나 같은 셀은 첫번째,두번째이므로 첫번째,두번째가 각각 TRUE로 표시되고 마지막은 650보다 큰 1200이므로 FALSE가 표시된다.

위와 마찬가지로

  • 수식입력줄에서 “(650<=D5:D7)”을 마우스로 선택하고
  • F9 키를 누르면 {FALSE;TRUE;TRUE}로 바뀐다.

위의 배열은 다음과 같 표현할 수 있다.

D5:D7영역에서 650보다 크거나 같은 셀은 두번째,세번째이므로 두번째,세번째가 각각 TRUE로 표시되고 첫번째는 FALSE로 표시된다.

이번에는 “((650>=C5:C7)*(650<=D5:D7))” 선택하고 F9키를 눌러보자.

{0;1;0}이 표시된다.

앞 수식의 결과와 뒷수식의 결과가 곱해진 결과이다.

엑셀에서

  • TRUE는 숫자 1과 같고
  • FALSE는 0과 같으므로
  • C열의 연산결과와 D열의 연산결과를 각각 곱하면
  • 최종적으로 두번째 행만 1이 구해진다.

650이라는 숫자가 포함되는 범위는 표에서 2번째 행이라는 뜻이다.

이제 2번째 행을 찾았으므로 두번째 행의 No.인 “A002″만 구하면 된다.

수식입력줄에서 “1/((650>=C5:C7)*(650<=D5:D7))”을 선택하고 F9키를 누르면
{#DIV/0!;1;#DIV/0!} 이 표시된다.

위에서 “((650>=C5:C7)*(650<=D5:D7))”을 연산하면 {0;1;0}이 되는데

숫자 1을 배열의 각 요소로 나누면 각각

1/0, 1/1, 1/0 이 되고 첫번째와 마지막은 0으로 나누어 #DIV/0!오류가 발생하므로

{#DIV/0!;1;#DIV/0!}이 구해진다.

이제 =LOOKUP(2,{#DIV/0!;1;#DIV/0!},B5:B7)가 무엇인지 보자.

다음 그림과 같이 {#DIV/0!;1;#DIV/0!} 배열에서 값을 찾았는데 2는 없으므로 2보다 작고 가장 가까운 값인 1을 찾고 B5:B7의 범위에서 같은 행에 있는 “A002″가 반환되는 것이다.

 

마지막으로 범위에 없는 값을 찾으면 어떻게 되는지 확인해 보자.

셀에 다음 수식을 입력한다.

=LOOKUP(2,1/((550>=C5:C7)*(550<=D5:D7)),B5:B7)

550은 어떤 범위에도 없는 값이므로 “#N/A”가 반환된다.


2. LOOKUP – 배열형

구문(Syntax)
LOOKUP(lookup_value, array)

LOOKUP의 배열형은 배열의 첫 번째 행이나 열에서 지정된 값을 찾고, 배열의 마지막 행이나 열의 같은 위치에서 값을 반환한다.

인수 :
– lookup_value : 찾는 값
– array : 찾는 범위, lookup_value와 비교할 텍스트, 숫자 또는 논리값을 포함하는 셀 범위이며 0,1,2…. A,B,C의 형태로 오름차순으로 정렬되어 있어야 한다. 대소문자는 구별하지 않는다.

LOOKUP 배열형 이해하기

LOOKUP의 배열형은 인수가 2개이다.

  • 첫번째 인수는 찾는 값이고
  • 두번째 인수는 찾는 범위(배열)이다. 배열의 첫 번째 행이나 열에서 지정된 값을 찾고, 배열의 마지막 행이나 열의 같은 위치에서 값을 반환한다.

다음 그림과 같이 점수별로 성적등급을 구하는 표가 있을 때 점수에 해당하는 성적등급을 구해보자.

F30셀에 다음 수식을 입력한다.

=LOOKUP(90,B30:C38)

90점에 해당하는 “A0″가 구해진다.

“A0″가 구해지는 과정을 그림으로 설명하면 다음과 같다.

  • 취득점수가 있는 범위 B30:C38 (array)에서
  • 90점을 찾아서(lookup_value)
  • array의 같은 행에 있는 마지막 열인 C열에서
  • “A0″를 찾는다

그런데 찾는 범위인 array영역을 잘못 지정하면 엉뚱한 값이 나오므로 주의가 필요하다.

F42셀에 다음 수식을 입력해 보자.

=LOOKUP(90,B42:D50)

90점에 해당하는 “A0″가 구해지는 것이 아니고 찾는 범위(array)의 마지막 열인 D열의 값인 “90 ~ 94″라는 문자열 값이 구해진다.

LOOKUP 배열형 함수는 찾는 범위(array)의 마지막에서 값을 구해주므로, 찾는 값이 마지막에 위치할 때만 사용해야 한다.

“90 ~ 94″가 구해지는 과정을 그림으로 설명하면 다음과 같다.

  • 취득점수가 있는 범위 B42:D50 (array)에서
  • 90점을 찾아서(lookup_value)
  • array의 같은 행에 있는 마지막 열인 D열에서
  • “90 ~ 94″를 찾는다

 

[ 엑셀 찾기 및 참조영역 함수 목록 ]

엑셀함수 LOOKUP – 행 또는 열의 같은 위치에 있는 값 찾기에 1개의 응답

댓글 남기기

이메일은 공개되지 않습니다.