엑셀에서 OFFSET와 MATCH 함수는 데이터를 동적으로 조회하고 특정 값을 반환하는 데 매우 유용합니다. 이 두 함수를 결합하면 복잡한 데이터 테이블에서 원하는 값을 효율적으로 찾을 수 있습니다. 오늘은 OFFSET과 MATCH의 기본 개념, 사용법, 그리고 실무 예제를 정리해 보았습니다.
OFFSET 함수란?
OFFSET 함수는 특정 셀 또는 범위를 기준으로 지정된 행과 열만큼 이동한 위치의 값을 반환합니다. 이 함수는 데이터를 동적으로 참조할 때 유용하며, 다음과 같은 형식을 가집니다:
OFFSET(reference, rows, cols, [height], [width])- reference: 기준이 되는 셀 또는 범위(필수).
- rows: 기준 셀에서 이동할 행의 수(양수는 아래로, 음수는 위로 이동).
- cols: 기준 셀에서 이동할 열의 수(양수는 오른쪽으로, 음수는 왼쪽으로 이동).
- height: 반환할 범위의 높이(행 수).
- width: 반환할 범위의 너비(열 수).
MATCH 함수란?
MATCH 함수는 특정 값이 배열 내에서 위치하는 인덱스를 반환합니다. 일반적으로 조회 작업에 사용되며, 다음과 같은 형식을 가집니다:
MATCH(lookup_value, lookup_array, [match_type])- lookup_value: 찾고자 하는 값(필수).
- lookup_array: 검색할 배열(필수).
- match_type: 일치 유형(-1, 0, 1 중 선택 가능).
OFFSET과 MATCH를 함께 사용하는 이유
OFFSET 함수는 기준 셀로부터 이동한 위치를 반환하지만, 이동해야 할 정확한 행 번호를 알아야 합니다. 여기서 MATCH 함수가 도움을 줍니다. MATCH를 사용하여 특정 값의 위치를 계산한 후 OFFSET에 전달하면 원하는 데이터를 동적으로 조회할 수 있습니다.
OFFSET MATCH 사용법
다음은 OFFSET과 MATCH를 결합하여 특정 값을 조회하는 기본적인 예제입니다:
예제 데이터
| 팀 이름 | 점수 | 어시스트 |
|---|---|---|
| Thunder | 25 | 10 |
| Lakers | 30 | 12 |
| Bulls | 20 | 8 |
목표
팀 이름 “Thunder”에 해당하는 점수를 조회합니다.
공식
=OFFSET(B1, MATCH("Thunder", A1:A11, 0)-1, 0)- B1: 반환 값이 있는 첫 번째 셀.
- MATCH(“Thunder”, A1:A11, 0): “Thunder”가 있는 위치를 찾습니다.
-1: INDEX가 1부터 시작하므로 실제 행 번호를 맞추기 위해 조정.- 0: 열 이동 없음.
결과
공식은 “Thunder”에 해당하는 점수인 25를 반환합니다.
실무 활용 예제
다중 컬럼 조회
특정 팀 이름에 대한 어시스트 값을 조회하려면 열 참조를 변경하면 됩니다:
=OFFSET(C1, MATCH("Thunder", A1:A11, 0)-1, 0)결과는 10입니다.
동적 데이터 분석
OFFSET MATCH를 사용하여 데이터가 추가되거나 변경될 때도 자동으로 업데이트되는 동적 범위를 설정할 수 있습니다. 예를 들어:
=SUM(OFFSET(A1, MATCH("Lakers", A1:A11, 0)-1, 0, 3))이 공식은 “Lakers”부터 시작하여 다음 세 개의 값을 합산합니다.
*참고
- 오류 처리
- OFFSET이 워크시트 경계를 벗어나면
#REF!오류가 발생합니다. - MATCH가 일치하는 값을 찾지 못하면
#N/A오류가 발생합니다.
- OFFSET이 워크시트 경계를 벗어나면
- 성능 고려
- 대규모 데이터셋에서는 OFFSET 사용이 느려질 수 있으므로 INDEX와 MATCH 조합을 고려하세요.
- 동적 배열
- Excel의 최신 버전에서는 동적 배열을 활용해 더 간단하게 작업할 수 있습니다.
정리하자면 엑셀 OFFSET과 MATCH 함수는 단순한 조회 작업을 넘어 복잡한 데이터 분석에도 활용될 수 있습니다. 이 두 함수를 잘 활용하면 효율적으로 데이터를 관리하고 분석할 수 있습니다. 실무에서 OFFSET MATCH를 적극적으로 사용해보시기 바랍니다.