엑셀 VLOOKUP을 이용하여 성적 등급 찾기

지난 포스팅에서 IF중첩 및 IFS를 이용하여 점수에 따른 등급을 산출하는 방법을 알아봤습니다.

IF 중첩을 간단하게 해주는 엑셀 IFS 사용법(IF에 조건 여러개), 오류처리 방법(IFERROR)

IF 중첩을 간단하게 해주는 엑셀 IFS 사용법(IF에 조건 여러개), 오류처리 방법(IFERROR)

엑셀 IF 함수 중첩 하기(조건 여러개 사용하기, AND, OR)

엑셀 IF 함수 중첩 하기(조건 여러개 사용하기, AND, OR)

 

위와 같은 IF중첩 및 IFS 방식은 등급별 점수 범위가 달라지면 IF안에 있는 조건을 전부 변경해 주어야 합니다.

그래서 등급별 점수가 있는 표를 참조하여 점수 조건이 달라져도 해당 표만 수정하면 반영되도록 해보겠습니다.

 

점수대 별 등급 IFS와 VLOOKUP 비교

 

녹색 사각형을 보시면 IFS를 이용하여 등급별 점수를 가져올 때에는 코드에 있는 80~90이라는 텍스트를 가져오기 때문에

3등급 점수가 80~65로 변경되어도 그대로 인 것을 볼 수 있습니다.

하지만 VLOOKUP은 왼쪽에 있는 표에서 점수 대역을 가져오기 때문에 표에서 3등급의 점수대를 바꾸면 점수도 따라서 변경되는 것을 알 수 있습니다.

이처럼 VLOOLUP을 사용하면 소스가 되는 데이터만 변경해도 수식을 변경할 필요가 없기 때문에 실수도 줄어들고 고생할 일도 줄어듭니다.

 

 

 

 

---

VLOOKUP을 이용하여 등급에 맞는 점수 가져오기

VLOOKUP의 기본적인 문법은 다음과 같습니다.

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

lookup_value는 조회하려는 값입니다.

table_array는 값을 검색할 셀의 범위입니다.

col_index_num은 table_array에서 가져올 값이 몇번째 열에 포함되는지를 넣어주는 것입니다.

range_lookup는 선택값이며 TRUE인 경우는 테이블의 첫 번째 열이 정렬되어 있어야 하며 가까운 값을 검색합니다.(유사 일치)

FALSE인 경우 정확한 값을 검색합니다. 기본값은 TRUE입니다.

 

엑셀 VLOOKUP 동작 방식

 

등급으로 점수를 찾는다고 가정하고 녹색 사각형(80~65)에 VLOOKUP수식을 입력합니다.

=VLOOKUP(K6,H2:I6,2,TRUE)

K6는 찾아오고 싶은 등급을 입력합니다. 3등급의 점수가 알고 싶으므로 3등급을 입력했습니다.

H2:I6는 데이터가 있는 테이블 범위입니다. 사각형의 왼쪽 위와 오른쪽 아래 셀을 각각 입력합니다.

2는 H2:I6에서 가져오고 싶은 데이터가 몇 번째에 있는지 나타내 줍니다. 

2라고 썼으므로 두 번째 열인 점수에서 값을 가져와 "80~65"가 됩니다. 

 

만약 1이라고 썼으면 첫 번째 열인 등급에서 값을 가져와 "3등급"이 될 것입니다.

그렇게 수행한 결과가 VLOOKUP을 입력한 셀에 보이게 됩니다.

빨간 테이블의 값이 변경되더라도 VLOOKUP은 수정할 필요가 없지만 범위가 변경되면 수정해야 합니다.

여러 가지 점수를 가져오기 위해 다음과 같이 변경하고 점수 열에 같은 VLOOKUP 함수를 사용할 것이므로 채우기 핸들을 드래그하여 나머지 열도 채워줍니다.

 

엑셀 자동 채우기

그랬더니 #N/A오류가 납니다.

엑셀 자동 채우기 오류

이런 경우 절대 참조를 이용하여 해결해야 합니다.

절대 참조는 다음 포스팅에서 알아보겠습니다.