1일1함수 보충(2-1): VLOOKUP 함수에서 주의할 점

지난 시간에 VLOOKUP함수에 대해 알아봤는데요, 도움말을 보면 주의할 사항들이 꽤 많이 나와있는데 건성으로 넘어간 것 같아서, 이번시간에는 주의할 점을 위주로 살펴보겠습니다.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP함수는 3~4개의 인수로 구성됩니다.

lookup_value, 첫번째 열에서 찾을 값

table_array, 데이타의 범위

col_index_num, 몇번째 열의 정보를 가져 올 것인가

range_lookup  정확한 값을 찾을 것인가, 근사값을 찾을 것인가

그런데, 도움말을 보니 그 뒤에 주의할 점이 있습니다.

• TRUE이거나 생략되면 정확한 값이나 근사값을 반환합니다. 정확하게 일치하는 값이 없으면 lookup_value보다 작으면서 그 다음으로 가장 큰 값을 반환합니다.

  table_array의 첫 번째 열 값은 오름차순으로 정렬해야 합니다. 그렇지 않으면 VLOOKUP 함수를 실행하여 올바른 결과를 얻을 수 없습니다. 자세한 내용은 데이터 정렬을 참고하십시오.

• FALSE이면 정확하게 일치하는 값만 찾습니다. 이 경우 table_array의 첫째 열에 있는 값을 정렬할 필요가 없습니다. table_array의 첫째 열에 lookup_value와 일치하는 값이 두 개 이상 있으면 먼저 발견된 값이 사용됩니다. 정확하게 일치하는 값이 없으면 #N/A 오류 값이 반환됩니다.

그러니까, 근사값을 찾도록 설정하면 반드시 첫째열을 오름차순으로 정렬해야하고, 옵션을 FALSE로 주어 정확한 값을 찾으라고 할때는 정렬할 필요가 없다는 말입니다.
또한,

• FALSE이면 정확하게 일치하는 값만 찾습니다. 이 경우 table_array의 첫째 열에 있는 값을 정렬할 필요가 없습니다. table_array의 첫째 열에 lookup_value와 일치하는 값이 두 개 이상 있으면 먼저 발견된 값이 사용됩니다. 정확하게 일치하는 값이 없으면 #N/A 오류 값이 반환됩니다.

즉, 찾아야 할 값과 같은 값이 첫번째 열에 두 개 이상이 있으면, 먼저 발견된 값이 사용된다는 것입니다. 그런데, 먼저 발견되는 것이 위에 있는 것일까요, 아래에 있는 것일까요?

언뜻 생각하기에는 위에 있는 값을 먼저 찾을 것 같은데, 확인해보니 아래에 있는 값을 먼저 찾았습니다.

보시는 것처럼, 첫열에서 1을 찾으라고 했더니, 3열에 있는 1을 먼저 찾아 98점을 반환했습니다.

그외에 첫열의 값이 숫자가 아니라 텍스트일 경우 주의해야할 점입니다.

• table_array의 첫 번째 열에서 텍스트 값을 검색할 때는 table_array의 첫 번째 열에 있는 데이터에 앞/뒤 공백을 두거나, 곧은 따옴표(' 또는 ")와 둥근 따옴표(‘ 또는 “)를 일관성 없이 사용하거나, 인쇄할 수 없는 문자를 사용하지 않도록 주의하십시오. 그럴 경우 정확하지 않은 값이나 예기치 않은 값이 반환될 수 있습니다. 자세한 내용은 CLEAN 및 TRIM을 참고하십시오.
• 숫자 값이나 날짜 값을 검색할 때는 table_array의 첫 번째 열에 있는 데이터를 텍스트 값으로 저장하지 않도록 주의하십시오. 그럴 경우 정확하지 않은 값이나 예기치 않은 값이 반환될 수 있습니다. 자세한 내용은 텍스트로 저장된 숫자를 숫자로 변환을 참고하십시오.
• range_lookup이 FALSE이고 lookup_value가 텍스트이면 와일드카드 문자인 물음표(?)와 별표(*)를 lookup_value에 사용할 수 있습니다. 물음표는 한 문자에 해당하고 별표는 개수에 상관없는 임의의 문자열에 해당합니다. 실제 물음표나 별표를 찾으려면 문자 앞에 물결표(~)를 입력합니다.

와일드카드를 사용할 수 있다는 것은 상당한 매력인 것 같습니다. 그런데, 두번째 주의할 점을 보면, "숫자값이나 날짜값을 검색할때는 첫번째열의 데이터를 텍스트값으로 저장하지 않도록 주의"하라고 되어있습니다. 이것이 무슨 말이냐하면, 숫자를 입력했어도 셀서식에서 텍스트값으로 지정되어있으면 엑셀은 그것을 숫자가 아니라 텍스트로 인식하기에, 숫자 1과 텍스트1은 다르다고 판단하여 틀린 값을 출력한다는 의미입니다.

D2셀에는 다음 수식이 들어갔습니다.
=VLOOKUP(1,$A$2:$B$6,2,FALSE)
수식에서는 1을 찾으라고 되어있으니, 언뜻 생각하기에 100의 값이 출력될 것 같지만, 실제로는 #N/A 오류를 출력합니다. 그 이유는 그림에서 보다시피, 여기의 1은 "텍스트형식으로 저장된 숫자"이기 때문에 그렇습니다. 오른쪽 클릭하여 셀서식에서 숫자로 지정해주면 해결됩니다.


p.s.2009/05/07 마지막으로, 틀리기 쉬운 것 하나!
범위를 지정할때, 첫열만 지정하는 분이 많습니다. 즉, 위의 수식을 다음처럼 쓰는 경우입니다.

=VLOOKUP(1,$A$2:$A$6,2,FALSE)

범위를 A열만 지정했으므로, A열에서 1이란 값을 찾아 오른쪽으로 두번째 열의 값을 찾는 위의 수식은 오류를 출력합니다. 왜냐구요? 지정된 범위가 A열뿐이므로, 오른쪽으로 더이상 갈수가 없지 않습니까?
오른쪽으로 두번째에 있는 값을 찾는다면 최소한 B열까지 범위를 주어야겠고,
=VLOOKUP(1,$A$2:$B$6,2,FALSE)
오른쪽으로 세번째에 있는 값을 찾는다면 최소한 C열까지 범위를 주어야하고
=VLOOKUP(1,$A$2:$C$6,3,FALSE)
오른쪽으로 열번째에 있는 값을 찾는다면 최소한 J열까지 범위를 주어야합니다.
=VLOOKUP(1,$A$2:$J$6,10,FALSE)

2009/03/18 - [컴퓨터/엑셀] - 1일1함수(2) : VLOOKUP 세로방향 데이터 목록에서 값 가져오기
2009/03/25 - [컴퓨터/엑셀] - 1일1함수(6) : HLOOKUP 함수 -- 가로방향 데이터 목록에서 값 가져오기