엑셀에서 연속되지 않은 구간의 값 찾기

다음 그림과 같은 엑셀 자료가 있을 때 특정 값이 어떤 구간에 존재하는지 찾아보겠습니다.

위 자료를 그림으로 표현하면 다음과 같습니다. 값이 연속되는 것이 아니라 중간 중간 비어 있는 구간이 있어서 원하는 값을 찾기가 어렵습니다.

 

예를 들어 650이라는 값은 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"가 반환되는 것입니다.

 

만약 범위에 없는 값 550을 찾으면 어떻게 되는지 확인해 보겠습니다.

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

550은 어떤 범위에도 없는 값이므로 "#N/A"가 반환됩니다.

 

 

---

관련 글