엑셀 함수 VLOOKUP 사용방법을 쉽게 알아보자

엑셀 함수 VLOOKUP 사용방법을 쉽게 알아보자

 

회사 다니는 분들이라면 누구든 활용해야 하는 프로그램.. '오피스(office)' 그중에 많이 사용하는 '엑셀'..

오늘은 그 '엑셀 함수'에 대해서 알아보도록 하겠습니다.

엑셀로 자료 정리 하시는 분들도 많습니다. 많이 사용되는 '합계', '평균' 등 여러 가지 수학적인 함수를 사용할 때도 있습니다. 

오늘은 많은 자료 중에서 원하는 키워드를 입력하면 그 키워드에 해당하는 자료들을 자동으로 찾아서 해당 입력칸에 기입되는 함수에 대해 알아보겠습니다.

바로 'VLOOKUP'이라는 엑셀 함수 입니다.

 

글로만 설명하면 불편함이 있으므로 실제 엑셀에서 제가 예시로 든 자료를 바탕으로 입력하여 VLOOKUP 함수를 사용한  스크린샷을 보면서 설명해 드리겠습니다.

 

 

일단 자료를 엑셀표로 정리를 해놓았습니다. 번호/이름/나이/학교/전공/키/몸무게/성별/나라 순으로 정리를 했습니다.

이제 이 표에 함수를 사용하여 원하는 표를 만들어보겠습니다.

 

 

 

'번호'를 입력하면 그 '번호'에 해당하는 이름/나이/학교/전공/키/몸무게/성별/나라가 제가 원하는 자리에 출력되도록 해보겠습니다.

'번호'를 입력했을 때 '이름'에  그 번호에 해당하는 모델이름이 출력하도록 하겠습니다.

출력할 칸에 마우스 클릭을 하여 포커스를 '이름'에 맞춥니다. 그리고 상단 메뉴 바로 아래 있는 'fx'라는 표시를 클릭합니다. 여기서 'fx'는 수학에서 나오는 '함수'라는 표기랑 같습니다. 이 'fx(함수)'를 클릭하면 '함수 마법사'라는 창이 출력됩니다. 그 '함수 마법사'창의 중간에 '함수 선택'칸에서 'VLOOKUP'이라는 함수를 선택 클릭하고 '확인'버튼을 클릭합니다.

 

 

 

'함수 인수'창이 출력됩니다. 이 창에는 엑셀표의 '이름'에 원하는 값이 출력될 조건식을 넣습니다.

첫째 칸 'Lookup_value'에는 제가 입력할 '키워드'의 칸 위치를 입력합니다. 저는 엑셀의 'A열 16행'에 해당하는 '번호'를 입력할 것이기 때문에 'A16'을 입력합니다.

 

 

 

둘째 칸 'Table_array'에는 제가 입력할 '키워드'에 대한 검색할 영역을 지정하여 줍니다.

표의 모든 영역을 검사할 것이기 때문에 'A열 1행부터 I열 11행'까지 드래그를 하여 영역을 표시해 줍니다.

그 표시는 'A1:I11'과 같이 입력하면 됩니다.

 

 

세 번째 칸 'Col_index_num'에는 제가 검색 키워드에 대한 출력될 값의 위치를 정하는 곳입니다. 'A열 16행'에 키워드를 입력하면 'B열 16행'에 그에 맞는 출력값이 나올 수 있게 하려면 '몇 번째 열'에 출력을 할 것인지 입력하여 주면 됩니다.

여기서 '이름'에 해당하는 'B16'은 시작열로부터 '2열'에 해당하기 때문에 숫자 '2'를 입력하여 줍니다.

 

네 번째 'Range_lookup'에는 제가 키워드를 입력 시 원하는 값이 정확하게 일치하는 것을 찾으려면 false(0)를, 비슷하게 일치하는 것을 찾으려면 True(1)을 입력합니다.

저는 정확히 일치하는 값을 출력되기 원하므로 '0'을 입력하겠습니다.

그리고 '확인'버튼을 눌러줍니다.

 

이렇게 '함수 인수'창의 각 칸에 해당하는 값을 넣어줘도 되지만, 엑셀 메뉴 아래 'fx'입력칸에 '=VLOOKUP(A16,A1:I11,2,0)'으로 직접 입력하여도 됩니다.

'함수 인수'창의 '확인'버튼을 클릭하여 마무리합니다.

 

 

 

그런데 모든 인수를 맞게 입력하였는데도 'B16'칸에는 '#N/A'라는 알 수 없는 표시가 출력됩니다.

이것은 제가 아직 키워드를 입력하지 않아서 출력할 값이 알 수 없기 때문에 출력되는 표시입니다.

 

 

 

그럼 '키워드'를 입력하여 보겠습니다. 제가 입력할 '키워드'자리는 'A16'이기 때문에 '번호1'을 입력해 보겠습니다.

'1'을 입력해 보았습니다. 함수로 만든 'B16'칸에 번호 1에 해당하는 '이름'인 '모델1'이 출력되었습니다.

 

 

 

그럼 이번에는 번호 '5'를 입력해 보겠습니다. 번호5에 해당하는 '모델5'가 자동으로 수정되어 출력됩니다.

 

 

 

그럼 위와 같은 방식으로 이름/나이/학교/전공/키/몸무게/성별/나라칸에도 'VLOOKUP'함수를 사용하여 해당하는 값들을 입력합니다. 여기서의 '키워드'인 '번호'를 입력하기 전까지는 모두 '#N/A'로 표시됩니다.

 

 

 

그럼 '키워드'를 입력할 칸에 번호 '1'을 입력해보겠습니다.

이름/나이/학교/전공/키/몸무게/성별/나라가 '번호1'에 해당하는 값들이 출력되었습니다,.

 

 

 

이번에는 번호 '5'를 입력해 보았습니다. 그러면 '번호5'에 해당하는 값들이 해당 행에 'VLOOKUP'함수로 입력시켰던 값들이 계산되어 출력됩니다.

 

 

 

이번에는 한 행만 함수를 사용하는 게 아니라 16행, 17행에 모두 같은 방법으로 'VLOOKUP'함수를 사용하여 출력칸을 만들어 보았습니다.

 

 

 

'번호'의 순서와 관계없이 '1번, 8번'을 입력하자 그에 해당하는 값들이 16행, 17행에 출력되었습니다.

 

 

 

이번에는 새로 다시 'VLOOKUP'함수를 사용하여 다른 표를 만들어 보겠습니다.

'fx'함수 버튼을 클릭하여 'VLOOKUP'함수를 선택 클릭하고 '확인'버튼을 클릭합니다. 

 

 

 

 

이번에는 '번호'가 아닌 '이름'을 키워드로 하는 표를 함수로 만들어 보겠습니다.

키워드 '이름'은 'B22'와 'B23'이 해당하겠습니다.

'나이'에 '함수 인수'를 입력해 보겠습니다. 이때 키워드는 '이름'이므로 'B열 1행부터 I열 11행'까지 'Table_array' 영역을 지정합니다. 왜 'A'열부터 영역을 지정하지 않는지에 대해서는 아래에서 설명하겠습니다.

 

 

 

키워드를 입력하는 곳은 'B'열입니다. 이보다 앞에 있는 'A'열은 검색 영역에 있어도 자료가 검색이 안됩니다. 그래서 검색이 되는 'B'열부터 'I'열까지 영역을 선택한 것입니다.

그래서 위 이미지의 엑셀 '이름으로 검색'표에서 '이름'에 '모델2'라고 키워드를 입력해도 'Col_index_num'이 2인 '나이'에는 해당 값이 출력되지만 이보다 앞에 있는 열인 'A'열은 검색에 해당이 안 되므로 'A'열은 제외됩니다.

 

 

 

그럼 '이름으로 검색'할 때 'B22'와 'B23'에 키워드를 넣고 'VLOOKUP'함수를 사용해 원하는 칸에만 함수를 적용시켜 보았습니다.

 

 

 

그 출력 결과를 보면 키워드 입력칸인 '이름'에 '모델3'과 '모델6'을 입력하자 함수로 만든 입력칸들에 키워드에 해당하는 값들이 출력되는 것을 볼 수 있습니다. 이때 모든 입력칸을 함수로 입력하여도 되고 필요가 없는 출력 칸이라면 함수사용을 하지 않고 빈칸으로 남겨도 키워드 출력에 상관은 없습니다.

다만 키워드 '이름'입력칸보다 앞에 있는 '번호'인 'A'열은 값이 출력이 되지 않는 것에 대해 참고하시길 바랍니다.

 

 

 

이번에는 '전공'으로 검색 키워드로 잡은 표를 만들어 보겠습니다..

전공 'A28', 'A29'에 키워드를 입력하면 해당하는 키/몸무게/성별/나라가 출력되도록 'VLOOKUP'함수를 사용해 보겠습니다.

'키(B28)'는 함수 fx =VLOOKUP(A28,E1:I11,2,0)

'몸무게(C28)'는 함수 fx=VLOOKUP(A28,E1:I11,3,0)

'성별(D28)'는 함수 fx=VLOOKUP(A28,E1:I11,4,0)

'나라(E28)'는 함수 fx=VLOOKUP(A28,E1:I11,5,0)

 

이렇게 해당하는 칸에 함수를 이용해 값을 출력하게 하였습니다.

 

 

 

키워드 '전공'으로 했을 시 검색영역은 (E1:I11)으로 선택합니다. '전공'이 첫째 Colume이 되면 됩니다.

 

 

 

'번호'로 검색할 때는 'A'열부터 영역을 표시했습니다.

 

 

 

'이름'으로 검색할 때는 '이름'이 해당하는 'B'열부터 영역을 시작했습니다.

 

 

 

그리고 '전공'으로 검색할 때는 'E'열부터 'i'열까지 영역을 선택하였습니다.

 

 

 

그리하여 위 엑셀 이미지처럼 키워드의 입력에 따라 값이 바뀌는 'VLOOKUP'함수를 사용하여 키워드에 따라 값이 바뀌는 엑셀 함수의 대표적인 예시를 볼 수 있었습니다.

 

 

 

'번호', '이름', '전공'으로 각각의 키워드에 따라 미리 함수로 계산해 둔 값들이 출력하는 것을 해보았습니다.

이는 꼭 그 자리에 지정 영역의 값들이 똑같이 들어가야만 하는 것은 아닙니다. 검색 키워드에 따라 다른 칼럼에 있는 값을 출력해도 되고 빈 출력칸이 있어도 됩니다. 하지만 보통은 그런 작업은 하지 않지요. 위와 같은 예시만 따라 해도 엑셀로 작업할 때 많은 도움이 될 것이라 생각됩니다.

 

천천히 따라 해보시고 이것저것 연습해 보시는 것을 추천드립니다.

뭐든 많이 연습하면 실력이 향상되니까요.

 

그럼 오늘의 포스팅을 마치겠습니다.

즐거운 하루 보내세요.^^