Issue with vlookup and 3 decimal places

[suttonutd]

Morning, very bizarre issue, hoping someone can assist.

Within an excel model, I have a few worksheets which are rate cards. Then I have a front sheet to enter selected Item Nos from the various rate cards. Once an item number is selected, a formula will go into each sheet and pick up item rate, based on Qty.

Issue: The rates are 3 decimal places. Once I type an item number within the front sheet, it returns #N/A. When I go into the rate card and manually type in the item number it then returns the correct value. Any ideas why this is? Both Sheets are typed as 12.01 and both sheets reflect 12.010.

Here is my formula:
=IF($B20<>"",VLOOKUP($B20,INDIRECT("'"&$A20&"'"&"!"&"$a:$X"),@MATCH(E20,INDIRECT("'"&$A20&"'"&"!"&"$1:$1"),1),FALSE),"")

Dissected:
If blank, return nothing.
Vlookup looks up Item number, in Cell B20, within workbook name, which is in A20. Match that grabs the Quantity within E20 and looks within the rate card within Row 1 and finds the nearest qty match as we have 4 columns of rates depending on quantity.

I have uploaded two images to assist. You may even know a more bullet proof formula.

Please help! Thanks, Steve

 

[JackDanIce]

Is the workbook in A20 open when you are entering data into the Front Sheet?
I believe you're missing the sheet named in the lookup workbook to know what sheet the lookup table is on.

Try:
=IF($B20<>"",VLOOKUP($B20,INDIRECT("'"&$A20&"'"&"!'Rate Card'"&"$A$1:$X$20000"),MATCH(E20,INDIRECT("'"&$A20&"'"&"!"&"$A$1:$X$1"),1),0),"")

Where Rate Card is name of sheet A1:X20000 is on

FALSE = 0, TRUE = 1 can be used argument value in a formula
Not sure why you have @ before MATCH
Usually better to limit ranges than use an entire column and entire row in arguments i.e. A:X vs A1:X20000

Or it may be the data types or cell formats with the input cells and lookup data aren't recognising values as numbers

 

[suttonutd]

Is the workbook in A20 open when you are entering data into the Front Sheet?
I believe you're missing the sheet named in the lookup workbook to know what sheet the lookup table is on.

Try:
=IF($B20<>"",VLOOKUP($B20,INDIRECT("'"&$A20&"'"&"!'Rate Card'"&"$A$1:$X$20000"),MATCH(E20,INDIRECT("'"&$A20&"'"&"!"&"$A$1:$X$1"),1),0),"")

Where Rate Card is name of sheet A1:X20000 is on

FALSE = 0, TRUE = 1 can be used argument value in a formula
Not sure why you have @ before MATCH
Usually better to limit ranges than use an entire column and entire row in arguments i.e. A:X vs A1:X20000

Or it may be the data types or cell formats with the input cells and lookup data aren't recognising values as numbers

Hi thanks for replying - all supporting sheets are within the same workbook. The @ sign is a new updated with Excel which annoys the hell out of me, nothing I can do about it.

 

[JackDanIce]

Apologies, misread your formula, it does include the sheet name, I thought it was looking at other workbooks to pull data from.

Have you tried to use Evaluate Formula (under formula menu) to try to isolate the issue within the formula?
Test each part of the VLOOKUP i.e.
=VLOOKUP($B20,'Rate Card'!$A$1:$X$20000,5,0)
If using full values like this also returns same error, then suspect it's a data formatting problem
If using full values like this shows you which part of the argument is causing error, you can then resolve there

 

[suttonutd]

Apologies, misread your formula, it does include the sheet name, I thought it was looking at other workbooks to pull data from.

Have you tried to use Evaluate Formula (under formula menu) to try to isolate the issue within the formula?
Test each part of the VLOOKUP i.e.
=VLOOKUP($B20,'Rate Card'!$A$1:$X$20000,5,0)
If using full values like this also returns same error, then suspect it's a data formatting problem
If using full values like this shows you which part of the argument is causing error, you can then resolve there

Hi, I have tried, and it is picking up exactly what I want it to but still returns #N/A. So the formula works but it just can't find the the look up value. Very confusing.