Vlookup함수 - 유사 일치

Vlookup 함수의 네번째 인수(range_lookup)는 1이면 유사 일치이고, 0이면 정확히 일치입니다.

정확히 일치하는 것은 쉬우므로

유사일치에 대해 사례를 들어 살펴보겠습니다.

 

vlookup함수 유사 일치.xlsx

0.01MB

 

 

 

1. 사례 1

가. 사례 분석

 

왼쪽에 일자별 제품별 판매가격이 있을 때

오른쪽 표에서 해당하는 판매가격을 찾는 것을 해보겠습니다.

 

왼쪽 표를 보면 A제품의 경우 2022-07-12부터 2022-12-14까지는 1,920원이고,

2022-12-25부터 2023-02-04까지는 2,075원,

2023-02-05부터는 2,085원이 적용됩니다.

 

B제품의 경우는 3행에 하나뿐이 없으므로 2022-08-14까지는 적용될 가격이 없는 것이고,

2022-08-15부터는 2,010원이 적용됩니다.

 

찾는 것은 매입일에 해당하고, 제품명이 맞는 판매가격입니다.

 

2. 해결 방법

따라서, 매입일이 판매가 적용일의 어느 구간에 해당하는 지를 찾아야 하고,

제품명이 일치해야 합니다.

 

가. 제품명 일치하는 행 찾기

제품명이 일치할 경우 판매가 적용일과 판매가격이 반환돼야 하므로

i2의 판매가격을 구할 때는

왼쪽 표의 제품명이 G2셀의 제품명과 일치해야 하므로

if(B2:B4=G2,A2:C5)라고 수식을 작성해서

B2에서 B4셀에서 G2셀과 일치할 때 A2셀에서 C5셀 중 원하는 행을 반환하면 됩니다.

 

나. 해당하는 판매가격 적용일 찾기

매입일이 F2셀에 있고, 찾을 범위(table_array)가 A2:C5셀로 위에서 제품명과 일치할 때 반환하는 범위와 같습니다.

 

그리고, 판매가 적용일이 정확한 값에서 다음 기간 전일까지 사이에 있는지 찾아야 하므로

유사 일치(1, True)로 네번째 인수 값을 지정해야 합니다.

 

따라서, 수식은

=VLOOKUP(F2,IF($B$2:$B$5=G2,$A$2:$C$5),3,1)가 됩니다.

제품명과 table_array는 행이 바뀌더라도 변하면 안되므로 절대 참조 형식으로 입력해야 하며,

판매가격이 3번째 있으므로 Vlookup함수이 세번째 인수값을 3이 됩니다.

 

 

A제품의 매입일 2022-07-15은 2022-07-12와 2022-12-24 사이에 있으므로 1,920이 되는 것입니다.

 

그리고, i2셀의 수식 셀 오른쪽 아래 네모 점을 i6셀까지 끌거나, 네모 점을 더블 클릭하면 수식이 모두 채워지는데,

 

B제품의 매입일 2022-12-25은 2022-08-15이후이므로 2,010원이 되는 것입니다.

 

 

2. 사례 2

가. 학점 기준표 변환

학점은 취득점수가 95점이상이면 A+에 4.5점이고,

90~94점은 A에 4.0점이 되는 식입니다.

 

그런데, Vlookup함수를 유사 값을 찾을 때는 구간이 오름차순으로 되어야 하고,

구간이라도 시작 값만 지정하면 되므로

E열과 같이 0, 60, 65, 70, 75, 80, 85, 90, 95라고 입력하고,

이에 해당하는 등급과 평점을 맞춰줘야 합니다.

 

나. 취득점수에 해당하는 등급과 평점 구하기

 

i10셀에 취득점수가 입력되면 해당 등급과 평점을 구해보겠습니다.

 

오른쪽으로 복사할 것이므로 취득점수가 있는 i열이 고정이어야 하고, table_array도 절대참조형식으로 입력합니다.

그러면 아래와 같이 되는데,

=VLOOKUP($I$10,$E$10:$G$18,2,1)

J10셀에 입력하면 85점에 해당하는 등급 B가 구해집니다.

 

J10셀의 채우기 핸들을 오른쪽으로 끌면 K10셀에 수식이 입력되는데,

평점이 B등급으로 이 표시됩니다. 이것은 세번째 인수 col_index_num이 2라서 그런 것이므로 

 

3으로 수정합니다.

=VLOOKUP($I$10,$E$10:$G$18,3,1)

 

 

또는 칼럼이 이동할 때 자동으로 col_index_num이 변경되도록 j2셀의 수식 중 2를 column()이라고 입력하면

J열의 열 번호 10이 구해지므로 2가 되도록 8을 빼줍니다.

그러면 수식은 =VLOOKUP($I$10,$E$10:$G$18,COLUMN()-8,1)이 되고,

 

J10셀의 채우기 핸들을 오른쪽으로 끌면 위와는 달리 평점 3.0이 구해집니다.

 

이것은 K열의 열 번호가 11인데, 8을 빼므로 3이 돼서 평점이 구해지는 것입니다.

 

 

다. 학점 기준표의 특이점

E10셀의 0을 지우고, i10셀에 50이라고 입력하면 60점 미만에 해당하는 점수를 구할 것 같은데, #N/A라고 표시됩니다.

 

그러나, 96점이라고 취득점수를 입력하면 위와는 달리 등급과 평점이 정확히 구해집니다.

 

따라서, 시작점수는 반드시 기입해야 하나,

95점이상의 종료 점수는 입력하지 않아도 된다는 것을 알 수 있습니다.

 

그러나 100점을 초과한 105점을 입력해도 A+가 나오므로

 

100점으로 제한해야 하는데, 이것은 학점 기준표로는 안되고, if 함수를 이용해 100점이하만 처리되도록 해야 합니다.

 

따라서, 수식은 =IF($I10<=100,VLOOKUP($I$10,$E$10:$G$18,COLUMN()-8,1),"에러")

i10셀의 값이 100이하일 때만 등급과 평점을 계산하고, 아니면 에러 문자를 표시하도록 했습니다.

 

vlookup함수 유사 일치(완성).xlsx

0.01MB