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
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