엑셀의 찾기 및 참조영역 함수는 표에서 값을 찾거나 참조를 가져올 때 사용합니다.
찾기 및 참조 영역 함수에는 표에서 원하는 값을 손쉽게 찾도록 도와주는 함수들이 많이 있습니다. 실무에서 많이 사용되는 VLOOKUP 함수부터 INDEX/MATCH, HLOOKUP, XLOOKUP과 같은 함수도 값을 찾을 때 많이 사용됩니다. 단순히 표에서 값을 찾는 것이 아니라 좀 더 복잡하고 동적인 방법으로 값을 찾거나 참조 범위를 지정해야 할 때는 엑셀에서 이해하기가 까다롭다고 하는 INDIRECT 함수와 OFFSET 함수를 사용해야 할 수도 있습니다.
이번 글에서는 찾기 및 참조 영역 함수의 개념 이해를 위해 주요 함수의 기본 사용법을 살펴보겠습니다. 함수별 자세한 사용법은 '함수별 사용법 페이지'를 참고하세요.
표에서 키 값에 해당하는 값 찾기
찾기 함수는 보통 다음과 같은 모양으로 된 표에서 특정 키값에 해당하는 값(예를 들어 상품코드 'A007'에 해당하는 상품명 또는 단가)을 찾을 때 사용합니다.
표의 왼쪽에 찾는 키값이 있고, 오른쪽 열에 찾는 값들이 있는 형태가 엑셀에서 가장 바람직한 표의 형태라고 할 수 있습니다.
표가 위와 같은 구조로 되어 있다면 다음과 같이 VLOOKUP 함수로 간단하게 값을 찾을 수 있습니다.
=VLOOKUP("A007",B4:D13,2,FALSE) => 'LCD모니터'
그러나 실무에서는 여러 이유로 표가 위와는 다른 형태일 수 있습니다. 이 때는 VLOOK 함수 대신 다른 복잡한 방법으로 해결해야 합니다.
아래와 같이 찾는 키 값이 표의 오른 쪽에 있다면 INDEX, MATCH 함수를 조합하거나 XLOOKUP 함수로 해결해야 합니다.
INDEX, MATCH 함수를 조합하여 해결한 예입니다.
=INDEX(B4:B13,MATCH("A007",D4:D13,0)) => 'LCD모니터'
INDEX 함수는 테이블 또는 배열에서 행과 열에 해당하는 값을 구해주고, MATCH 함수는 지정된 범위에서 찾고자 하는 값의 위치를 찾아줍니다.
위의 예에서는 MATCH함수가 상품코드 'A007'이 입력된 행의 위치를 찾아주고, INDEX 함수는 MATCH 함수의 결과인 7('A007'이 범위에서 7번째 행에 있음)을 받아서 [B4:B13] 범위의 7번째 행에 있는 'LCD모니터'를 가져와서 표시해 줍니다.
엑셀 2021 버전 또는 Microsoft 365 버전을 사용한다면 XLOOKUP 함수를 사용해서 해결할 수도 있습니다.
=XLOOKUP("A007",D4:D13,B4:B13) => 'LCD모니터'
XLOOKUP 함수는 VLOOKUP의 몇 가지 단점(찾는 키값이 항상 1열에 있어야 하고, 가로로 된 표는 VLOOKUP 대신 HLOOKUP 사용 등)을 해결하고 사용하기 쉽게 개선된 엑셀의 새로운 찾기 함수입니다.
INDIRECT 함수와 OFFSET 함수
실무에서는 표에서 값을 찾는 것만 있는 것이 아니라 다음과 같은 문제를 해결해야 하는 경우도 있습니다. 이 때는 찾기 및 참조영역 함수 중에 INDIRECT 함수, OFFSET 함수를 응용하여 해결할 수 있습니다.
- 드롭다운 목록 만들기(INDIRECT 함수)
- 시트명을 바꿔가면서 값 찾기(INDIRECT 함수)
- 동적으로 월별 누계 구하기(OFFSET 함수)
- 동적 범위에 이름 정의하기(OFFSET 함수)
INDIRECT 함수로 드롭다운 목록 만들기
INDIRECT 함수는 문자열로 만들어진 참조를 유효한 셀 참조로 바꿔줍니다.
문자열을 유효한 셀 참조로 바꿔주는 INDIRECT 함수의 기능과 엑셀의 데이터 유효성 검사 기능을 조합하면 아래 그림과 같이 자료를 조회할 때 필요한 드롭다운 목록을 만들 수 있습니다.
자세한 내용은 '데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기' 글을 참고하세요.
OFFSET 함수로 동적 월별 누계 구하기
OFFSET 함수는 어떤 셀로부터 행과 열을 이동한 후 동적으로 참조를 구해줍니다. 참조를 동적으로 구하는 기능을 이용하면 월 단위 판매실적 누계를 구할 수 있습니다.
7월까지의 서울지역 판매실적 누계는 다음과 같이 OFFSET 함수를 사용하여 구할 수 있습니다.
=SUM(OFFSET(C5,0,0,D20,1)) => 7,551,000
자세한 내용은 'OFFSET 함수로 월별 누계 구하기' 글을 참고하세요.
기타 찾기 및 참조영역 함수
위에서 설명하지 않았지만 다음과 같은 찾기 및 참조영역 함수들도 자주 사용됩니다.
| 용도 | 함수 |
| 행과 열 다루기 | ROW - 시트에서 행의 번호를 구해줍니다. 행 번호를 이용하면 자료에서 번호를 자동으로 매기거나 행의 위치 값을 구해서 다양한 방법으로 응용할 수 있습니다.
COLUMN - 시트에서 열의 번호를 구해줍니다. 엑셀에서 표를 만들 때 표의 타이틀에 월을 표시하는 경우가 많은데, COLUMN 함수를 응용하면 중간에 열이 삭제되어도 월의 순서가 유지되도록 할 수 있습니다. |
| 정렬, 필터링, 중복 제거
※ Microsoft 365, 엑셀 2021 이상 버전에서만 사용 가능 |
SORT - 범위 또는 배열의 내용을 정렬합니다. 원본 데이터를 손대지 않고 별도의 범위에 정렬된 결과를 표시할 수 있습니다.
SORTBY - 범위 또는 배열의 값을 기준으로 범위 또는 배열의 내용을 정렬합니다. 출력결과에 포함되지 않은 범위를 정렬기준으로 설정할 수 있고, 정렬기준을 여러 개 지정할 수 있습니다. FILTER - 배열 또는 범위에서 원하는 조건으로 데이터를 조회(필터링)합니다. UNIQUE - 범위에서 중복을 제거한 결과 또는 한번만 입력된 값(유일한 값)을 구해줍니다. |
| 기타 | CHOOSE - 나열된 값 목록에서 원하는 위치의 값을 반환합니다.
HYPERLINK - PC의 하드디스크, 네트워크서버에 저장된 문서나 인터넷 주소로 이동하는 링크를 만들어 줍니다. TRANSPOSE - 배열이나 범위의 행과 열을 바꿉니다. |
