엑셀 | vlookup 하지 마세요. (?)

엑셀은 프로그래밍 언어도 아닌 것이 셀에 명령어를 쓰면 사전에 정의된 형식에 맞다면 명령에 따라서 실행을 하는 것처럼 보인다.

흔히 ‘함수를 사용한다.‘고 하는데 엑셀에서 함수가 없었다면 엑셀이 인기 있을 이유가 없었을지도 모른다.

엑셀의 함수는 너무나 많아서 엑셀의 함수가 얼마나 많은지 알 수 없을 지경이다. 그 중 ‘찾기 참조 영역’ 함수에 해당하는 vlookup은 엑셀을 처음 배우기 시작하면서 알게 되고 ‘엑셀 참 좋구나’ 라고 처음 생각하게 하는 기능이었다. MOS 자격증 시험 문제로써 풀어야 해서 알 수 밖에 없었는지도 모르겠다.

vlookup의 친구 hlookup도 있으나 data는 옆으로(열로) 길게 되어 있기 보다는 아래로(행으로) 길게 되어 있는 것이 일반적으로 많은 형태이므로 hlookup 보다는 vlookup이 많이 사용된다. (이러한 형태에 대한 고민은 tidydata¹라는 용어가 특별히 있을 정도이다.)

VLOOKUP

VLOOKUP의 구문(Syntax)

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

범위(table_array)의 첫번째 열을 수직으로 내려가면서 키값(lookup_value)를 찾은 다음, 같은 행에 있는 지정된 열(col_index_num)의 값을 반환한다.

인수 :

• lookup_value : 범위에서 원하는 값을 찾기 위한 키값
• table_array : 값을 찾을 범위
• col_index_num : 값을 찾을 범위에서 가져올 값이 있는 열의 위치
• range_lookup : 일치하는 키값을 찾을 것인지 근사값을 찾을 것인지 결정(TRUE-근사값,FALSE-일치하는 값)

VLOOKUP의 구문을 보면 table_array라는 것이 있다. 값을 찾을 범위를 지정하고 그 범위에서 lookup_value를 찾아서 몇 번째 열(col_index_num)에 있는 값을 가져올지 정해주면 vlookup의 함수는 table에서 값을 찾아서 참조해 준다.

결과를 잘 가져다 주니 되었다. - 이렇게 생각하면 아무 문제가 없을 것 같다.

그러나 vlookup의 불편한 점이 바로 여기에 있다. table을 지정해 주어야 한다는 것이다.
table의 크기는 아주 클 수도 있고 table을 특정하기가 애매할 수도 있고 tidydata가 아니라서 옆으로(열) 길게 늘어져 있을 수도 있다.

데이터가 잘 정돈 되어 있는 table에서 찾아 오게 한다면 찾을 값(lookup_value)에서 몇 칸 옆에 떨어져 있는 값을 가져와야 하는지가 한 눈에 보여 1이나 2 등으로 즉각 지정해 줄 수 있으나 table이 옆으로 길게 늘어져 있는 data라고 한다면 찾을 값에서 몇 번째 열의 위치를 가져오라고 지정해 주어야 하는지 쉽게 세기 어려울 수도 있다.

어떤 똑똑하신 분들은 이럴 때 몇 번째인지 세어 보기 위하여 엑셀의 하나의 행을 할당하여 1,2,3을 쓰고 마우스로 채우기 기능을 사용해 옆으로 드래그 하여 숫자가 나열된 행을 만들어서 몇 번째 행인지 확인하고 vlookup에 열의 위치(col_index_num)를 입력하는 것을 본 적이 있다.

vlookup은 엑셀 2003에서나 사용하던 철 지난 함수라는 생각이다. 그 무렵에 MOS 자격증 시험을 응시했었으니 이 후로 그 시험에서도 vlookup 문제가 없어졌기를 바란다. (물론 없어지지 않았다고 해서 문제가 될 것은 없기는 하다.)

엑셀 2007 이후로는 vlookup과 hlookup을 대체할만한 기능이 생겼다. 2007년이면 2021년 현재 무려 14년이나 지난 연도이다.

index, match

vlookup과 hlookup을 대신할 훌륭한 함수는 바로 index와 match 함수이다.

vlookup 을 index와 match로 사용하는 것이 훨씬 편하다.

vlookup을 굳이 쓰기 위해 대형 table을 table로 지정하고 몇 번째 열을 가져와야 하는지 확인하기 위해 번호를 매기고 하는 엑셀 사용자의 등 뒤에서 그 모습을 우연히 보게 되면 가슴이 답답해지곤 했다.

• index, match는 이렇게 생각하면서 실행하면 아주 직관적이고 아무때나 원하는 값을 찾아서 옆에 가져다 둘 수 있다.

값을 찾아다 놓고 싶은 셀에 위치하고 다음을 순차적으로 타이핑과 클릭을 한다.(익숙해 지면 몇 초면 한다. 아주 강력하다.)

=index(가져올 값이 있는 열 전체를 클릭, match(비교할 값 하나를 클릭, 비교 대상이 있는 열 전체를 클릭, 0 ))

예를 들면, 사람별로 점수가 있는데 특정인의 점수를 가져오고 싶다면 가져다 두고 싶은 곳에서 =index( 를 쓰고 점수 열의 index를 통째로 눌러 C:C 가 되도록 하고 ,match( 를 쓰고 비교 할 대상이 되는 이름 셀을 하나 클릭해서 참조하고 ,를 입력한 다음 비교 대상이 있는 열 전체 B열의 index를 클릭해서 B:B를 나오게 한 후 ,0)) 를 써서 Enter로 완성하면 가져오고 싶은 곳에서 값을 찾아 오게 된다.

(1) =index( 타이핑 후 C 열 클릭

(2) ,match( 를 쓰고 비교 할 대상이 되는 이름 셀 하나 클릭

(3) 비교 대상이 있는 열 전체 B열의 index를 클릭

(4) 완성

이제 상황을 확장하여 이름과 점수는 300,000행이고 비교 대상의 아래로 200,00개의 행에 해당 값을 찾아 와야 한다고 가정한다. (그림의 예시는 3개 중 2개를 찾아오지만 우리의 현실은 많은 것을 클릭 한번에 해결해야 한다.)

(5) 채우기 드래그 (우측 하단 십자가 더블 클릭)

다음은 index와 match의 구문이다. 작동 원리를 충분히 이해한다면 원리를 알 수 있으나 사실은 어떻게 활용하면 될지만 알면 결과를 도출하는데는 충분하다.

간단하게만 이해를 하면 match가 행 index 또는 열 index를 반환해 주면 그 것을 index가 찾는 key로 사용하여 찾아낸다는 내용이다.

index 의 구문(Syntax)

= INDEX ( 배열/범위, 행번호, [열번호], [배열번호] )

• 배열/범위 : 배열형식의 상수/목록 또는 참조할 셀 범위 (참조형의 경우 여러개의 범위를 지정할 수 있습니다.)
• 행번호 : 반활할 값의 행 번호
• 열번호 : [선택 인수] 반환할 값의 열 번호
• 배열번호 : [선택 인수] (여러개 범위를 입력했을 시) 값을 반환할 배열의 번호

match의 구문

= MATCH ( 찾을값, 범위, [일치옵션] )

• 찾을값 : 범위에서 찾을 값.
• 범위 : 찾을값을 검색할 범위. 범위의 행과 열의 넓이 중 하나는 반드시 ‘1’이여야 한다. (가로/세로로 긴 형태) 그렇지 않을경우, MATCH 함수는 #N/A 오류가 난다.
• 일치옵션[선택인수] : 찾을 값을 검색할 방법. 기본값은 ‘1’²