VLOOKUP Issue with Approximate Match - Need Help Categorizing Customers

[happydz]

I'm facing an issue with the VLOOKUP function when attempting to categorize my customers based on their values. I have a list of customer names in column A and their respective values in column B. Additionally, I have a reference table with categories in column G and their corresponding values in column H. The categories are sorted in ascending order, as follows: F (0), D (60), C (70), B (80), A (90).

I'm using the following VLOOKUP formula to categorize the customers based on their values:
=VLOOKUP(B2, G2:H6, 1, TRUE)
The problem is that when I look up for the value B2 in the range H2:H6 for the approximately value, the result was 0 although I noticed that the value of the customer (57) is near to the value in the reference table 60 for the category D.

I've double-checked my data, ensured that the cell formatting is correct, and confirmed there are no leading/trailing spaces. Despite these checks, the problem persists.

I've uploaded a screenshot of my Excel sheet to provide a clearer understanding of the issue. Any assistance or insights you can provide would be greatly appreciated.

 

[awoohaw]

When using aproximate match argument as TRUE VLOOKUP will give the last record that was met. Since 57 never reaches 60 it give you 0. You can see that in other examples in your image. If you have later versions of excel the XLOOKUP and XMATCH functions have arguments that allow you to pick the next higher or next lower for the best match.

Please update your profile with what version of excel you are using so the forum can give you suggestions that fit your version.

Best Wishes.

 

[happydz]

When using aproximate match argument as TRUE VLOOKUP will give the last record that was met. Since 57 never reaches 60 it give you 0. You can see that in other examples in your image. If you have later versions of excel the XLOOKUP and XMATCH functions have arguments that allow you to pick the next higher or next lower for the best match.

Please update your profile with what version of excel you are using so the forum can give you suggestions that fit your version.

Best Wishes.

Thank you for your reply, my Excel version is Microsoft office standard 2010. Version: 10.0.1460.1000 (32 bits)

 

[awoohaw]

Then you may want to do your matching differently as 2010 doesn't go to the next value. Wrapping IFERROR around an INDEX-MATCH formula may help. But this assumes that you want the next highest number even if there is match (See 60 in this example).

It looks like you're doing a grading scale. Why not try this:

=IF(B2<=60,60,IF(B2<=70,70,IF(B2<=80,80,IF(B2<=90,90,100))))

 

[happydz]

Then you may want to do your matching differently as 2010 doesn't go to the next value. Wrapping IFERROR around an INDEX-MATCH formula may help. But this assumes that you want the next highest number even if there is match (See 60 in this example).

It looks like you're doing a grading scale. Why not try this:

=IF(B2<=60,60,IF(B2<=70,70,IF(B2<=80,80,IF(B2<=90,90,100))))

I tried excel online google docs, but it gave the same result, however, my goal is to learn the VLOOKUP formula and the grading scale is just an example. Thank you anyway for your help. Please, keep this thread opened so that the forum may help with suggestions to use vlookup in this regard.

 

[Sam_D_Ben]

Please check:

Book2
	A	B	C	D	E	F
1
2		Data	Result		Lookup
3		57	60		0
4		66	70		60
5		98	90		70
6		100	90		80
7		76	80		90
8		0	0
9		64	70
10		55	60
11		88	90
12
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=IF(MIN(IF($E$3:$E$7>=$B3,$E$3:$E$7))=0,VLOOKUP($B3,$E$3:$E$7,1,1),MIN(IF($E$3:$E$7>=$B3,$E$3:$E$7)))

Thankx
Sam

 

[awoohaw]

VLOOKUP is becoming more and more outdated. I suggesting using INDEX-MATCH, which is much like XLOOKUP in the newer versions of Excel, except it doesn't have the updated matching arguments.

And VLOOKUP will behave the same regardless of the version.

Finally, there are youtube channels that can really help you. Take a look at these:
Mr Excel, ExcelISFun (very comprehensive video list), Leila Gharani, and MyOnlineTrainingHub
Most have sample workbooks with before and after versions

 

[happydz]

Please check:

Book2
	A	B	C	D	E	F
1
2		Data	Result		Lookup
3		57	60		0
4		66	70		60
5		98	90		70
6		100	90		80
7		76	80		90
8		0	0
9		64	70
10		55	60
11		88	90
12
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=IF(MIN(IF($E$3:$E$7>=$B3,$E$3:$E$7))=0,VLOOKUP($B3,$E$3:$E$7,1,1),MIN(IF($E$3:$E$7>=$B3,$E$3:$E$7)))

Thankx
Sam

thank you sam

 

[happydz]

VLOOKUP is becoming more and more outdated. I suggesting using INDEX-MATCH, which is much like XLOOKUP in the newer versions of Excel, except it doesn't have the updated matching arguments.

And VLOOKUP will behave the same regardless of the version.

Finally, there are youtube channels that can really help you. Take a look at these:
Mr Excel, ExcelISFun (very comprehensive video list), Leila Gharani, and MyOnlineTrainingHub
Most have sample workbooks with before and after versions

thank you

awoohaw​

 

[Peter_SSs]

my Excel version is Microsoft office standard 2010.

Please put that information in your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

(57) is near to the value in the reference table 60 for the category D.

If you are looking for the closest match (up or down) then would this work for you?

23 11 05.xlsm
	B	C	D	E	F	G	H
1	Data	Result				Category	Value
2	57	D				F	0
3	66	C				D	60
4	98	A				C	70
5	100	A				B	80
6	76	B				A	90
7	0	F
8	64	D
9	55	D
10	88	A
Lookup Category

Cell Formulas
Range	Formula
C2:C10	C2	=INDEX(G$2:G$6,MATCH(MIN(ABS(B2-H$2:H$6)),ABS(B2-H$2:H$6),0))
Press CTRL+SHIFT+ENTER to enter array formulas.