가로세로 VLOOKUP, 클릭 3번으로 가능!
마우스 클릭 3번으로 구현 할 수 있는 VLOOKUP 가로세로 찾기 기능 알아보기
이 강의에서는 매트릭스 형태의 표에서 자주 마주치는 가로·세로 동시 조회 작업을, VLOOKUP 함수 대신 엑셀의 '선택 영역에서 만들기' 기능과 참조연산자만으로 처리하는 방법을 다룹니다. INDIRECT 함수로 셀 주소를 동적으로 참조해 #NULL! 오류를 해결하는 응용 사례와, 누적된 이름정의 범위를 한 번에 정리하는 관리 팁까지 함께 정리합니다.
실습자료를 준비했어요
수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇
목차 보기
가로세로 VLOOKUP 찾기, 클릭 3번이면 OK! :: 엑셀프레소 13강
- 가로세로 조회에 활용할 참조 범위를 머릿글까지 포함하여 선택합니다.
- 리본 메뉴에서 '수식' - '선택 영역에서 만들기'를 클릭합니다.
- 대화상자에서 '첫 행'과 '왼쪽 열' 항목이 모두 체크된 상태로 '확인' 버튼을 클릭합니다.
- [ =값1 값2 ] 형식으로 가로·세로로 참조할 머릿글을 띄어쓰기로 연결해 입력하면, 두 범위가 교차하는 값이 결과로 반환됩니다.
- 이름이 정의된 머릿글이 아닌 일반 셀 주소를 그대로 참조하면 #NULL! 오류가 반환됩니다.
- 이런 경우 각 셀 주소를 INDIRECT 함수로 감싸 참조하면 이름정의 범위로 정상 인식되어 올바른 값을 가져올 수 있습니다.
- 리본 메뉴에서 '수식' - '이름관리자'로 이동합니다. (단축키 Ctrl + F3)
- 첫 번째 이름정의 범위를 선택한 뒤 Shift 키를 누른 채 마지막 이름정의 범위를 클릭하면, 두 항목 사이의 모든 범위가 한 번에 선택됩니다.
- '삭제' 버튼을 클릭한 뒤 확인 대화상자에서 '확인' 버튼을 누릅니다.
- 마지막으로 '닫기' 버튼을 눌러 작업을 마무리합니다.
자주묻는질문
자주묻는질문1. [ =값1 값2 ]의 동작원리는 무엇인가요?
엑셀의 참조연산자(=범위연산자)는 다음과 같이 세 종류로 구분됩니다.
| 연산자 | 설명 |
| : | 두 개의 셀 사이를 하나의 범위로 지정합니다. (예: A1:A3, B1:C10 등) |
| , | 두 범위를 하나로 병합합니다. 단, 범위가 겹치는 셀은 중복으로 계산됩니다. (예: A1:A3, A1:C1 '→ 여기서 A1은 중복되므로 두 번 계산됩니다.) |
| (띄어쓰기) | 두 범위가 교차하는 셀을 반환합니다. (예: A1:A3 A2:B2 '→ A2가 반환됩니다.) |
'수식' - '선택 영역에서 만들기' 기능을 사용하면 선택 범위의 '첫 행'과 '왼쪽 열' 값을 이름으로 한 각각의 범위가 자동 생성됩니다. 따라서 [ =서울 매장당매출 ] 처럼 입력하면, '서울' 범위와 '매장당매출' 범위가 교차하는 '19,640,000' 이 결과로 반환됩니다.
자주묻는질문2. 이름정의범위가 많을 경우, 파일크기나 동작속도에 영향을 미칠 수 있나요?
10,000행 × 5열 규모의 데이터로 직접 테스트한 결과, 이름정의범위 추가 전 396KB → 추가 후 513KB 로 약 117KB 증가한 것을 확인했습니다. 동작 속도의 경우 최초로 이름정의범위를 생성할 때 약 3초 정도의 지연이 발생했지만, 이후 일반 작업에서는 사용자가 체감할 만한 속도 저하는 발견되지 않았습니다.
다만, 보다 효율적인 작업을 위해 참조해야 할 데이터의 양이 많다면 이번 강의에서 소개한 이름정의범위 방식보다는 VLOOKUP/MATCH 또는 INDEX/MATCH 함수 조합을 활용하는 것을 권장합니다.
가로세로 VLOOKUP 궁금한점이 있어서요.
실무에서 왼쪽열이 날짜인 경우가 많은데요.
왼쪽열이 날짜인 경우 위 강의대로 진행하니 조회가 안되더라구요.
해결방법이 없을까요?
아쉽게도 이름정의범위는 숫자로 시작할 수가 없습니다 ㅜㅜ..
따라서 왼쪽열이 날짜인 경우는 해당 방법을 이용할 수 없고 VLOOKUP + MATCH 또는 INDEX/MATCH 공식을 이용해주셔야 합니다.
얼핏아는거라고 생각했는데 이런식으로도 쓸수있군요.