VLOOKUP partially working

I am using a vlookup function to find the high and low limits of a value given the selected "type" in a data validation drop down. The first column of the lookup table, and the data validation dropdown list both come from the same source range. This range is selected from another workbook.

Basically the vlookup works on some of the drop down options but not all of them. When I use the evaluate formula tool to try and diagnose the problem it shows that the item from the drop down is not found in the first column of the table. I'm not sure how this is possible given they are both linked to the same range.

The formula looks as follows:

Please Login or Register  to view this content.

Cell B23 is the data validation drop down cell, array A3:AC13 (on worksheet 'Gradation Limits') is the lookup array.

I use the IF function to return #n/a if there is no limit given in the table. This allows me to graph the limits.

I can't create an example that replicates my problem so I haven't attached anything. I'm just wondering if there is some problem I'm missing with because I pull the range data from another workbook.

Probably your VLOOKUPs are missing the 4th optional argument of FALSE to find exact matches:

=IF(VLOOKUP($B$23,'Gradation Limits'!$A$3:$AC$13,2,FALSE)="",NA(),VLOOKUP($B$23,'Gradation Limits'!$A$3:$AC$13,2,FALSE))