엑셀 vlookup 등급 점수에 맞춰 자동으로 매기는 방법

얼마 전 시험 점수표를 엑셀로 옮기는데, 학생마다 등급을 손으로 입력하려니 80점과 90점 경계에서 자꾸 헷갈리더라고요. 기준표를 한 번 만들고 VLOOKUP을 쓰면 점수가 바뀌어도 등급이 자동으로 따라옵니다. 인사 평가나 판매 실적을 구간별로 나눌 때도 같은 방식으로 쓸 수 있어요.
점수보다 먼저 등급 기준표 만들기
학생 이름이 A열, 점수가 B열, 등급이 C열에 있다고 가정할게요. 시트 한쪽의 E열과 F열에 등급 기준표를 만듭니다. E1에는 ‘최소 점수’, F1에는 ‘등급’을 적고, 그 아래를 다음 순서로 채웁니다.
- E2 0점, F2 F등급
- E3 60점, F3 D등급
- E4 70점, F4 C등급
- E5 80점, F5 B등급
- E6 90점, F6 A등급
여기서 E열의 숫자는 그 등급이 시작되는 점수입니다. 80점을 받은 사람은 B등급이고, 79점을 받은 사람은 C등급인 셈이죠. 기준표를 높은 점수부터 적고 싶을 수 있지만, VLOOKUP으로 점수 구간을 찾을 때는 최소 점수를 0, 60, 70, 80, 90처럼 작은 값부터 배열해야 합니다. 기준표가 엉키면 수식이 멀쩡해 보여도 예상과 다른 등급이 나올 수 있어요.
VLOOKUP 수식으로 등급 자동 입력하기
C2 셀에 =VLOOKUP(B2,$E$2:$F$6,2,TRUE)를 입력해 보세요. B2는 등급을 매길 점수, $E$2:$F$6은 기준표, 2는 기준표의 두 번째 열인 등급을 가져오라는 뜻입니다. 마지막 TRUE는 점수와 정확히 같은 숫자가 기준표에 없어도 알맞은 구간을 찾도록 합니다.
예를 들어 B2가 87이면 기준표에 87이라는 숫자가 없어도 87 이하인 최소 점수 중 가장 큰 80을 찾습니다. 그래서 결과는 B등급입니다. B2가 정확히 90이면 A등급이 나오고, 69.5라면 D등급이 나옵니다. 마이크로소프트의 VLOOKUP 안내에서도 이런 근사값 검색은 기준표 첫 열을 오름차순으로 놓아야 한다고 설명합니다.
수식의 달러 기호도 중요해요. C2의 수식을 아래로 복사하면 점수 위치는 B3, B4로 바뀌지만, 기준표 범위는 $E$2:$F$6에 고정됩니다. 달러 기호를 빼면 복사할 때 기준표 범위까지 한 줄씩 밀려서 뒤쪽 학생의 등급이 틀어질 수 있습니다. C2 셀 오른쪽 아래의 작은 사각형을 아래로 끌거나 더블클릭하면 여러 행에 한꺼번에 적용할 수 있어요.
TRUE와 FALSE가 헷갈릴 때
등급표에서는 마지막 인수를 TRUE로 써야 합니다. TRUE는 기준점과 정확히 일치하지 않는 점수도 해당 구간에 넣는 근사값 검색입니다. 반대로 FALSE는 정확히 일치하는 값만 찾습니다. 87점을 FALSE로 검색하면 기준표에 87점 행이 없으므로 등급 대신 #N/A가 표시됩니다.
VLOOKUP의 마지막 인수를 생략해도 기본값은 근사값 검색이지만, 등급 수식에는 TRUE를 직접 적는 편이 읽기 편합니다. 나중에 파일을 다시 열었을 때 어떤 기준으로 찾는지 바로 보이거든요. 다만 학생 번호나 상품 코드처럼 값이 정확히 일치해야 하는 목록을 찾을 때는 FALSE가 알맞습니다. 같은 함수라도 ‘점수 구간을 찾는가, 특정 항목 하나를 찾는가’에 따라 마지막 인수가 달라집니다.
등급이 이상하게 나오면 확인할 곳
먼저 기준표의 최소 점수가 숫자인지 보고, 0부터 90까지 오름차순으로 놓였는지 확인하세요. 점수를 다른 곳에서 붙여 넣었다면 숫자처럼 보여도 문자로 저장됐을 수 있습니다. 이런 경우에도 검색 결과가 예상과 달라질 수 있어요. 기준표를 정렬할 때는 E열만 움직이지 말고 등급이 적힌 F열까지 함께 선택해야 점수와 등급의 짝이 유지됩니다.
#N/A가 뜬다면 점수가 기준표의 가장 작은 값보다 낮은지도 확인해 보세요. 위 예시처럼 0점부터 기준표를 시작하면 일반적인 0~100점 시험에서 그 문제를 피할 수 있습니다. 반대로 101점이 입력되면 가장 높은 기준인 90점에 해당해 A등급이 나옵니다. 100점을 넘는 값이 오류라면 등급 수식만 믿기보다 점수 입력 범위를 0~100으로 제한하거나 원자료를 확인하는 게 좋습니다.
실제로 써보면 등급을 매기는 일보다 기준을 분명하게 적는 일이 더 중요합니다. ‘90점 이상은 A’처럼 규칙이 바뀌면 기준표의 숫자만 고치면 되고, 여러 사람의 등급도 한 번에 따라 바뀝니다. 점수표를 자주 다룬다면 작은 기준표 하나가 손으로 고치는 시간을 꽤 줄여줍니다.
