[Excel] 엑셀 vlookup 자동 열번호 (match 그리고 표)

vlookup의 약점 열번호

vlookup은 '만약 없었으면 회사생활을 어찌했을까' 싶을 만큼 최애 엑셀 함수입니다.

vlookup 함수는 입력해야 할 인수(Arguments)가 좀 많은 편이고 어떤 분들께는 익숙해지지 않는 함수이기도 합니다.

 

또 익숙해진 분들은 약간 아쉬움이 생기는 부분이 있는데, 바로 열 번호 (col_index_num) 때문입니다.

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

열 번호는 참조 표에서 몇 번째 열의 데이터를 끌어올지 정하는 값인데, 보통 숫자로 입력하며 참조 표에서 몇 번째에 해당하는지 손가락을 꼽으며 세어야 하는 게 문제입니다. (A, B, C 읽으며 손가락으로 세기 ㅎㅎ) 표의 열이 많은 경우에 세는 것도 일이죠. 잘 세어봐도 자꾸 틀리는 경우가 많아 몇 번 입력 값을 수정하는 경우가 허다합니다.

 

그리고 참조 표 자체가 양식이 확정되지 않아 수시로 열이 추가/삭제되는 경우 vlookup 수식에서 열 번호를 계속 바꿔줘야 하는 고통이 따릅니다.

 

그래서 그 해결 방법을 알려드리고자 합니다. 자동으로 열 번호를 찾아오는 법!! (동적 열 참조 / Dynamic Column Reference)

예를 들어 참조 표가 아래와 같은 양식이라 하고, vlookup으로는 특정 제품의 폭 값을 불러오고자 한다면 오른쪽과 같이 수식을 입력하면 됩니다. 아주 정상적이고 바람직한 수식입니다. 참조 표의 범위는 움직이지 않도록 F4를 눌러 행렬 고정을 하는 게 좋겠죠!

그런데 제품의 정보가 자꾸 추가되어 '높이', '중량', '색상', '재질' 등등 계속 많아진다면 열의 수가 화면을 벗어날 수도 있습니다. 손가락이 모자랄 수도 있어요 ㅎ

 

표의 등장

그래서 저는 이렇게 응용해봤습니다.

일단 익숙하지 않으실지도 모르겠지만 표(table) 기능을 이용합니다. 엑셀이 어차피 다 표인데 무슨 표냐고 하실지 모르겠네요.

 

참조 표 영역의 셀을 선택하시고 [Ctrl + T] 단축키를 누르면 다음과 같은 팝업이 생깁니다. 머리글 포함은 '제품 / 폭 / 길이' 부분을 머리글로 인식하도록 하는 것으로 체크를 하셔야 해요. 단축키가 아닌 메뉴를 사용하여 표를 만드실 경우, 삽입에서 표를 클릭하시면 됩니다.

 

그럼 아까 만든 표가 이렇게 바뀝니다. 뭐가 바뀌었냐면, 표의 서식이 예쁘게 그것도 자동으로 표현이 됩니다. 표를 사용하지 않으시는 분들은 앞으로 많이 활용을 해보시길 바래요. 데이터가 지속적으로 추가되는 경우 표의 범위가 자동으로 확장되어 굉장히 편리해집니다. 테두리, 채우기 서식 수작업에서도 완전히 해방되는 거예요. 만약 파란색 계열이 싫다! 그러시다면, 

다음과 같이 표를 클릭한 뒤 메뉴의 표 디자인 그리고 표 스타일(style)에서 마음에 드시는 서식을 선택하시기만 하면 됩니다.

 

짜잔!

스타일은 얼마든지 있으니 마음에 드시는 걸 꼭 찾으실 수 있을 거예요.

vlookup 얘기하려다가 표 기능 소개에 분량을 너무 많이 쓴 것 같네요. 너무 좋은 기능이라...

 

match와 vlookup 콤비

표를 적용했으면, 이제 vlookup을 진화시켜봅니다. 참조 표의 열 번호는 match 함수를 이용해서 찾아옵니다. vlookup도 안 익숙한데 갑자기 match 함수를 말씀드려 죄송합니다... ㅠ_ㅠ 하지만 포기하지 마세요!! 쉽습니다. vlookup을 사용하신 다면 match도 비슷하고 더 쉽게 구동되니까요.

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value : 찾으려는 값
• lookup_array : 열 또는 행 범위
• [match_type] : vlookup 마지막 인수와 같은 것으로 완전히 일치하는지에 대한 옵션

vlookup도 match도 매치 타입에 0 (false)를 입력하는 게 일반적입니다. 우리는 완전히 일치하는 경우만 찾을 거니까요. 특정 범위에서 내가 찾고자 하는 값이 몇 번째 있는지를 반환해주는 것이 바로 match 함수입니다. vlookup의 열 번호도 단순히 참조 표 첫 번째 열이 1번이고 순서대로 2번, 3번인데, match도 단순 1번, 2번을 반환하니 여기에 써먹을 수가 있는 거예요.

 

이름 관리자

표를 사용하면 수식이 좀 복잡해집니다. 셀의 주소가 아닌, 표의 이름과 머리글의 값을 이용하여 참조토록 표 수식으로 표현됩니다. 복잡해 보이지만 직접 입력하는 게 아니니 겁먹지 마세요! 표는 자동으로 표1, 표2 이렇게 이름이 지정되는데, 메뉴 중 수식의 이름 관리자(Name Manager)에서 마음에 드는 이름으로 변경하실 수 있습니다.

 

일반 vlookup 수식과 동적 열번호 수식 비교

오른쪽 파랑 표의 A제품의 수식은 아까 입력한 아주 평범하고 바람직한 수식입니다.

그리고 C 제품의 수식은 제가 소개해드리고자 매우 애쓰고 있는 수식의 예입니다.

 

수식을 설명드리면, 파랑 표의 C값 [@제품 = 수식을 입력하는 셀이 포함된 표에서 '제품'열의 '동일 행'의 값]을 까만 표[표 1]에서 찾고자 하며, 까만 표의 머리글에서 파랑 표의 머리글 중 길이를 찾아 몇 번째 열인지 정하는 것입니다. 이과 출신이 글로 설명하자니 엉망이네요. 또 욕먹을 것 같은 예감...

 

동적 열번호 구현 따라하기

수식을 클릭으로 입력하는 것을 알려드릴게요. 사실 기존에 하는 방법으로 하시는 거랑 같은데, 표가 익숙지 않기에 절차를 하나하나 보여드리고자 합니다.

 

=vlookup( 까지 입력하고, 바로 옆의 C를 클릭합니다. 다음 인수를 입력하기 위해 쉼표는 꼭 입력하시고요!

참조할 표의 범위를 선택합니다. 머리글을 빼고 선택하시고, 수식에서 깔끔하게 표1과 같은 이름이 표시되면 잘하신 겁니다.

이제 대망의 자동 열 번호인데요, 일단 match( 까지 입력합니다. 그리고 찾고자 하는 값인 길이를 선택합니다. 파랑 표의 머리글 부분입니다.

 

이제 match 함수가 참조할 행/열 범위를 선택합니다. 까만 표의 머리글에 해당합니다. match_type은 정확히 일치하도록 0으로 입력합니다.

match함수의 괄호를 닫아주고, vlookup 함수의 match_type도 0으로 입력 후 마지막 괄호를 닫습니다.

 

vlookup의 진화 (표 + 동적 열번호)

그럼 계산 결과를 한번 볼까요?

C의 길이는 10이 맞는데, A의 길이는 30이지만 10을 표시하고 있습니다.

일반적인 vlookup 수식은 이런 일이 발생합니다. 단순히 참조 열 번호를 초기에 2로 입력했기 때문에, 희망하는 길이가 아닌 폭의 값을 가져온 것이고, match를 사용한 자동 열 번호는 표가 변화하더라도 제대로 결과를 표시해줄 수 있습니다. 제대로 표시하려면 수식의 2를 3으로 고쳐줘야 하죠.

 

새로운 수식을 썼을 땐 파랑 표의 길이를 폭으로 바꿔주면 그에 해당하는 결과가 나옵니다. A제품도 폭 열을 참조하고 있었고 C 제품은 자동으로 폭열 번호를 당겨오니 결과적으로 문제가 없는 표가 되었네요. 

 

이제 데이터를 추가합니다. 중량 데이터를 추가하고 파랑 표의 머리글 열을 중량으로 수정했습니다.

A제품의 값은 아까 전과 동일합니다. 당연히 안 바뀝니다. 하지만 C 제품은 새로 입력된 15가 정확히 표시가 됩니다.

 

 

데이터가 추가되었지만, 기존 수식은 추가된 데이터를 범위에 포함시키고 있지 않습니다.

하지만 표 수식을 이용한 경우에는! 확장된 표의 범위에 맞게 참조를 하고 있습니다.

여기까지 표와 match를 이용해 vlookup을 한 단계 진화시키는 방법을 소개해드렸습니다.

 

감사합니다.

 

본 글 내용뿐만 아니라 기타 office 문의에 대한 문의도 댓글 남겨주시면 당일 답변드립니다. 편하게 남겨주세요!

 

정보가 마음에 드셨다면 즐겨찾기 또는 구독 그리고 좋아요 부탁드려요!