엑셀의 검색, 참조 관련 함수

마이크로소프트 엑셀(Microsoft Excel)에는 검색, 참조와 관련된 함수가 많이 있습니다. 이들을 잘 활용하면 편리할 때가 많습니다. 그 중 몇 가지 예를 들어 보겠습니다.

CHOOSE

CHOOSE 함수는 최대 254개의 값 중 index에 해당하는 값을 반환하는 함수입니다.
Syntax: CHOOSE (index_num, value1, value2, ...)

예를 들어 =CHOOSE(3, B4, B5, B6, B7, B8, B9)라고 하면 리스트에서 세 번째 항목인 B6 셀의 값이 반환됩니다.

이와 같이 =SUM(B4:CHOOSE(3, B4, B5, B6, B7, B8, B9))라고 하면 =SUM(B4:B6)과 같은 의미를 갖습니다.

INDEX (array)

INDEX (array) 함수는 주어진 배열에서 행과 열을 제시하면 그 값을 반환하는 함수입니다.
Syntax: INDEX (array, row_num, col_num)

A4:B9 배열에서 4행 2열의 값은 113입니다.

A1과 B1의 값을 각각 2와 1로 바꾸면 2행 1열의 값인 Banana를 가져오게 됩니다.

열의 개수를 하나로 줄이고 나면 몇 번째 행을 검색하는지만 알려주면 됩니다.

INDEX (reference)

INDEX (reference) 함수는 앞서 살핀 index (array) 함수와 유사하지만 영역을 선택할 수 있는 옵션이 추가됩니다.
Syntax: INDEX (reference, row_num, col_num, area_num)

본 예에서는 A4:C6과 A7:C9 두 개의 영역을 모두 참조하고 있습니다. 이때 맨 마지막 필드인 area_num을 1로 하느냐, 2로 하느냐에 따라 어떤 영역의 값이 반환될지 결정됩니다. 여기에서는 첫 번째 영역의 2행 3열 값인 56이 반환되었습니다.

하지만 area_num을 2로 바꾸면 두 번째 영역인 A7:C9의 2행 3열 값인 32가 반환됩니다.

행이나 열을 지정할 때 그 값을 0으로 설정하면 열이나 행 전체를 선택한다는 의미입니다. 위 보기에서 =SUM(INDEX(A4:C9, 0, B1))은 B1의 값이 3이므로 3열 전체를 포함한다는 의미입니다. 이는 곧 =SUM(C4:C9)와 같은 의미입니다.

MATCH

MATCH함수는 특정 배열(열이나 행)에서 주어진 아이템의 값을 검색해 해당 아이템의 상대 위치를 반환하는 함수입니다.

Syntax: MATCH (lookup_value, lookup_array, [match_type])
이때 match_type이 0이면 정확히 일치하는 값을 찾으라는 옵션이며, match_type이 1이거나 생략되면 lookup_value보다는 작지만 가장 가까운 값을, -1이면 lookup_value보다는 크지만 가장 가까운 값을 찾을 때 씁니다. 여기에서 중요한 점은, lookup_array는 반드시 오름차순으로 정렬되어 있어야 한다는 것입니다. 오름차순으로 정렬되어 있지 않을 때에는 제대로 된 결과를 반환하지 못할 수 있습니다.

B1 셀을 보면 A1의 값인 "Apple"을 A3:A6 영역에서 검색하고 있습니다.

숫자 역시 마찬가지입니다.

match_type에 -1을 넣었더니(크지만 가장 가까운 값) 제대로 된 값을 찾아내지 못합니다.

그래서 A3:A7 영역의 값을 내림차순으로 정렬해 주었더니 그제서야 제대로 찾아 냅니다.

LOOKUP (vector)

LOOKUP (vector) 함수는 1행 혹은 1열짜리 벡터에서 원하는 값을 찾아 그에 대응하는 벡터에서 값을 반환해 주는 함수입니다.
Syntax: LOOKUP (lookup_value, lookup_vector, result_vector)

위 예에서처럼 Cherry의 수량이 몇 개인지 알고 싶다면, A1의 값인 Cherry를 품목 벡터인 A4:A9 벡터에서 찾고 결과값은 수량의 벡터인 C4:C9에서 반환하면 되는 것입니다.

이때 중요한 점은, lookup_vector의 값은 반드시 오름차순으로 정렬되어 있어야 한다는 점입니다. 위와 같이 lookup_vector의 값을 내림차순으로 정렬하기만 해도 Lookup 함수가 제대로 된 값을 도출하지 못한다는 것을 알 수 있습니다.

LOOKUP (array)

LOOKUP (array) 함수는 배열에서 원하는 값을 찾아 값을 반환합니다. 이때 반환하는 값은 배열의 크기에 따라 달라집니다.
Syntax: LOOKUP (lookup_value, array)

A1의 값을 찾되 array를 A열에서 C열까지 지정했더니 A열에서 Cherry를 찾아 C열의 값인 24를 반환하고 있습니다.

배열의 범위를 B열까지로 좁히면 반환하는 값도 B열의 값인 0.42가 됩니다.

검색어로 Grapefruit를 주고 A4:A9를 배열로 주니 Grape가 반환됩니다.

VLOOKUP

앞서 살핀 LOOKUP 함수의 예를 통해 유추할 수 있겠지만, VLOOKUP 함수는 배열의 특정한 열(row → vertical)에서 값을 검색하는 함수입니다.
Syntax: VLOOKUP (lookup_value, table_array, column_index_number, range_lookup)

앞서 LOOKUP 함수는 배열의 크기에 따라 반환되는 값이 달라졌습니다. 하지만 VLOOKUP 함수는 column_index_number를 통해 반환될 열(column)을 지정할 수 있습니다. 위 그림에서는 검색할 값이 A1 즉 Apple이며 table_array는 A4:C9, 반환할 열은 두 번째 즉 B열입니다. 따라서 반환값은 0.72가 됩니다. 이때 중요한 점은 table_array의 첫 번째 열은 반드시 오름차순으로 정렬되어 있어야 한다는 것입니다.

VLOOKUP 함수의 맨 마지막 인수인 range_lookup은 정확한 값을 찾을 것이냐, 아니면 비슷한 값을 찾을 것이냐를 설정하는 부분입니다.이 값을 생략하거나 TRUE로 지정해 주면 가장 비슷한 값을 찾아 그 결과를 반환합니다. 그렇지 않고 FALSE로 설정할 경우에는 정확한 값만을 찾아 그 결과를 반환합니다.

이때 column_index_number는 시작하는 열이 어디냐에 따라 정해지는 상대적인 값입니다. 앞서 VLOOKUP(A1, A4:C9, 3)과 같이 table_array를 A, B, C열로 설정한 경우에는 세 번째 열이 C열이지만, 이처럼 VLOOKUP(A1, B4:D9, 3) table_array가 B, C, D열을 참조하고 있을 때 세 번째 열은 C열이 아닌 D열입니다.

HLOOKUP

VLOOKUP 함수가 table_array에서 특정한 열의 값을 반환하는 것과 달리 HLOOKUP 함수는 table_array의 특정한 행의 값을 반환합니다. 사용법은 VLOOKUP 함수와 동일합니다.
Syntax: HLOOKUP (lookup_value, table_array, row_index_number, range_lookup)

INDEX-MATCH

VLOOKUP이나 HLOOKUP도 나름의 장점이 있지만 그보다는 INDEX와 MATCH 함수를 함께 써서 VLOOKUP이나 HLOOKUP을 대체하기를 많이 권하고 있습니다. (Why INDEX MATCH is better than VLOOKUP, How to use INDEX MATCH instead of VLOOKUP)

그 이유를 보면 몇 가지가 있는데, 우선 VLOOKUP이나 HLOOKUP은 결과값을 선택할 열이나 행이 수식 내에 고정되어 있다는 점입니다. 이로 인해 오류를 일으킬 확률이 높아집니다. 또한 VLOOKUP이나 HLOOKUP의 table_array 중간에 열이나 행을 삽입하게 되면 수식을 다시 작성하거나 수정해야 하는 번거로움이 있습니다. 반면 INDEX-MATCH를 사용하면 그와 같은 문제가 없습니다. 다음으로 VLOOKUP이나 HLOOKUP은 첫 번째 행이나 열에서만 값을 검색할 수 있습니다. 하지만 INDEX-MATCH를 사용하면 그러한 제약도 없습니다. 게다가 VLOOKUP이나 HLOOKUP에 비해 처리해야 할 테이블이나 배열의 크기도 작아질 수 있습니다.

VLOOKUP의 경우 A3:D12를 대상으로 하고 있습니다.

위 VLOOKUP을 INDEX-MATCH로 대체한 경우입니다. A열과 D열만 참조하고 B, C열은 참조하지 않습니다. 만약 C열과 D열 사이에 새로운 열을 삽입하면 VLOOKUP의 경우 수식을 수정해 주어야 하지만 (현재는 참조열이 4이지만 중간에 열이 하나 끼어들었으므로 5로 수정해 주어야 합니다) INDEX-MATCH는 별도로 손을 대지 않아도 됩니다.

이처럼 INDEX 함수와 MATCH 함수를 함께 사용하면 VLOOKUP 함수나 HLOOKUP 함수가 할 수 있는 일을 모두 하면서도 더 유연하며 가벼운 업무 처리가 가능합니다.