엑셀 vlookup함수 (예제는 전화번호 찾기 예시로)

안녕하세요. 적시우입니다.

 

제가 이번에 다룰 소재는 엑셀의 vlookup 함수에 대해서 알아보도록 하겠습니다. 

 

브이룩업 함수는 직장인이 꼭 알아야 할 함수로 알면 엄청엄청나게 좋은 함수입니다. 

엑셀 파일에서 여러개의 항목들을 찾을 때 쓰기 좋은 함수입니다.

 

 

LOOKUP앞에 V가 붙어있는데요. V를 이해하면 더 사용하기 쉽습니다. V는 VERTICAL 수직의, 세로라는 뜻입니다.

저는 V가 밑으로 쏠리는 모양 때문에 위 아래로 찾는다고 이해하고 있습니다.

위아래로 돌아다니면서 찾는 함수라고 생각하시면 편합니다.

 

또 다른 함수인 HLOOKUP함수의 H는 HORIZONTAL 수평의, 가로라는 뜻입니다. H모양의 - 모양을 보고 가로라고 이해하고 있습니다. 

옆으로 돌아다니면서 찾는 함수라고 생각하시면 편합니다.

 

 

유의사항이 있는데요.

 

첫열이 오름차순으로 정렬이 필수입니다.

동명이인처럼 중복된 값을 찾는값으로 잡는 경우 누락된 값이 나올 수 있기 때문에 구하려는 값은 중복이 되지않는 값으로 하셔야 됩니다.

(예를 들어 이름보다는 학번이나 사용자코드 이런값)

그리고 달러 표시라하죠. $표시 참조를 잘하셔야합니다. 참조표시는 F4를 누르면 만들어집니다..

 

$A$1는 행과 열을 둘다 고정시켰다고 보면 됩니다.

$A1는 행을 고정시켰다고 보면 됩니다. A$1는 열을 고정시켰다고 보면 됩니다. 

저는 수학시간때 배운 행렬을 떠올려서 앞에 $가 있으면 행이 고정됐고 뒤에 $가 있으면 열이 고정됐다고 외우고 있습니다.

 

 

 

 

엑셀파일에서 필요한 전화번호를 찾는 예시를 들어보도록 하겠습니다.

 

 

 

 

 

절차는 이렇습니다. 1대1로 아래 내려가면서 매칭을 시킨다. 

 

 

찾는값 2019B를 베낄값 2019B랑 일치시킨다. 지금 상태는 1번 슬롯, 1번가방이라고 이해하시면 편합니다.

이와같은 과정으로 찾을값 여러개가 선택이 됐다고 칩시다.

 

 그 다음 선택된 값을 Col_index 기둥(가방)값에 따라 바꿔준다고 생각하시면 됩니다.

베낄값의 범위중에서 첫열을 기준으로해서 1,2,3 이런식입니다. (고정이 아니고 상대적입니다.) 

 

일치된 값에서 번호에 해당하는 슬롯(가방)을 가서 이름만 바꿔준다.

1번은 코드 2번은 이름 3번은 가짜전화번호 4번은 국적으로 순서가 되있기때문에 번호가 나옵니다.

기준은 TABLE ARRAY(베낄 값)이기때문에 찾는값과 베낄 값의 범위를 코드가 아니라 이름을 기준으로하면 가짜전화번호는 2번으로 바뀝니다.

(상대적인 번호입니다.)

 

 

수식을 써봤습니다.

 

일단 처음은 무조건 오름차순 정렬인데요.

베낄 값의 첫열을 오름차순 해주셔야됩니다.

 

저는 코드가 이름과 다르게 중복되지 않고 유일한 값이기 때문에 코드를 기준으로 찾으려고 합니다.

코드값을 찾을 것이기 때문에 베낄 값에서 코드 부분을 오름차순 정렬을 해주세요.

 

 

코드에 해당하는 셀을 누르고 마우스 오른쪽 - 정렬 - 사용자 지정 정렬을 눌러주세요. 

A열이니까 열은 A 정렬기준은 값 오름차순을 해주세요.

 

 

 

그 다음 찾을 값의 옆에다가 =vlookup을 입력해주세요.

(시프트 F3으로 함수마법사를 킨 화면입니다. 설명을 보고 하셔도 됩니다.)

 

편의상 번호로 구별했습니다. 

 

1번 찾는 값을 입력해주세요. 

한개만 입력하시고 나머지는 다하신 후에 드래그하시면 됩니다.

그래서 저는 2019B를 찾을 것이기 때문에 그에 해당하는 H10을 선택했습니다.

 

2번. 베낄값 테이블의 범위를 정해야하는데요. 

 

저는 A5 : D22까지인 셀에서 긁어올 것이기 때문에 범위를 잡았습니다. 

그리고 F4를 누르면 $A$5:$D$:22로 변하는데요.

(행열 모두다 고정으로 이해하시면 편합니다.)

 

이게 뭐냐면 고정시키는 역할을 한다고 이해하시면 편합니다.

만약에 H10값을 구한다음 H11을 드래그해서 채운다고 칩시다.

 참조가 되어있지 않으면 한칸 밀린 A6:D23으로 범위를 잡기때문에 H10값은 제대로 나옵니다만.

 H11 값부터는 원하는 값을 얻을 수 없습니다. 그래서 참조($)를 반드시 해주셔야 됩니다.

 

 

3번. COL_INDEX NUM 말 참 어렵게 하는데요. 

2번 과정에서 베낄값의 범위를 잡았잖아요. 

그 범위의 첫열이 1번 다음열이 2번 또 그 다음열이 3번 이런식으로 올라갑니다.

(열은 엑셀에서 세로값을 말합니다. ABCDE이런식으로 말이죠.)

 

그러니까 1번은 코드 2번은 이름 3번은 가짜전화번호 4번은 국적이 됩니다.

그러니 3을 입력해주면 됩니다.

 

 

마지막으로 RANGE-LOOKUP인데 정확하냐 유사하냐 가려야합니다.

번호는 정확할 수 밖에 없으니까 FALSE로 해주시면 됩니다.

정확하면 FALSE 유사하면 TRUE입니다.

(왜 이런표현을 쓰느냐 의아해 할 수도 있는데 제 생각으로는 컴퓨터도 이해하고 사람도 이해하는 방식으로 표현한 것이라고 생각합니다.) 

 

먼가 복잡하게 설명됐습니다만 찾을값을 지정하고 베낄값의 범위를 참조하고 가방을 바꾸고 정확하냐 유사하냐만 지정해주면 값이 잘 나오는 것을 알 수 있습니다.

 

읽어주셔서 감사합니다.