VLOOKUP returning an #N/A when it should return a value

[Nate Westcott]

Hello,

I have attached a workbook to this post that uses this formula:

=VLOOKUP(C5,Sheet2!$B$1:$C$99,2,0)

in Sheet1. The value in C5 is a value produced from another formula. I've carefully checked my work, but I am still getting an error. Can anyone tell me what I am doing wrong? I encounter a similar problem from time to time, and I'm hoping this will help me understand how this formula works a little better. I have a feeling that this has to do either with cell format, or placement of the cell with the formula in relation to the data it is reading.

Big thanks to anyone who can help at all with this!

[Jonmo1]

The value in C5 is not EXACTLY 3.1
It's actually 3.125

You can see this by formatting C5 to show 3 dedimals.

Formatting it to show only 1 decimal makes it 'Appear' to be rounded to 3.1, but the actual value 3.125 is still in the cell.
And your vlookup is using that actual value.

Also, you referenced the range B1:B99, and specified 2 as the column index.
There is only 1 column in B1:B99, that should probably be B1:C99

Try either:
=VLOOKUP(ROUND(C5,1),VALIDATION!$B$1:$C$99,2,0)

Or apply the round to the average function in C5
=ROUND(AVERAGE(L13:L20),1)

[Richard Buttrey]

Hi,

There are two problems

1. C5 is an AVERAGE function and returns numbers with decimals e.g. 3.125. This value isn't in your Lookup table. Change C5 to7

Please Login or Register  to view this content.

2. The Lookup range VALIDATION!$B$1:$B$99 is only one column wide, i.e. column B. You are wanting a value from the 2nd column, hence the range should be as wide as your table. i.e. B1:F99

[TMS]

There are two problems: you are trying to return column 2 from a single column array (B1:B99); and you are trying to match a value generated by an AVERAGE calculation against a range of values that doesn't include, for example, 3.125

This works:

Please Login or Register  to view this content.

You might want to round your result, ROUND, ROUNDUP, or ROUNDDOWN) and make it an EXACT match (parameter 4 = FALSE)


Regards, TMS

[MrShorty]

As an additional thought, instead of rounding, you could change the 4th argument in your VLOOKUP() function to TRUE or 1 (https://support.office.com/en-us/art...8-93a18ad188a1 help file to discuss the different behavior of the 4th argument). This works because your lookup values are sorted in ascending order. The exact choice of solution will probably depend on exactly what you want to happen at your boundary conditions. When the average returns 2.55..., the proposed ROUND() functions will return 2.6, which will cause the VLOOKUP() to return "Very good". My solution will leave the average below 2.6, which means the VLOOKUP() will return "Good". You will need to decide what you want to happen at these boundary conditions.

[TMS]

@MrShorty: agreed