Part of VLOOKUP not working

  • Thread starter Thread starter Code Numpty
  • Start date Start date
C

Code Numpty

Sorry to post like this but I cannot see what is wrong with the following
nested VLOOKUP formula.

=IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)),
IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)),
IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)),
IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)),
IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0))

If the contents of B3 are on any sheet except
[Accounts.xls]Export_US_Dollars the formula returns the correct data. If B3
is on [Accounts.xls]Export_US_Dollars the formula returns FALSE.

The Export_US_Dollars worksheet has just been added, hence the change in
formula, it worked fine before. Can anyone point me in the right direction?
 
Insert the condition for TRUE at this IF part for "Export_US_Dollars", eg:
,"",
... IF(ISNA(VLOOKUP(...Export_US_Dollars'!$A:$G,2,0)),"",
VLOOKUP(...Export_US_Dollars'!$A:$G,2,0)), ..

It was missing in your posted formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
 
After this part:

IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)),

you need to put in what you want to happen if the data is not found -
usually this will be:

"",

before the next bit, so that you have got something like this:

IF(ISNA(vlookup),"",vlookup)

Hope this helps.

Pete
 
Thanks Max! I had stared and stared at this and not realised that I'd
overwritten that when I edited the original. Many thanks for taking the time
to set me straight.

Max said:
Insert the condition for TRUE at this IF part for "Export_US_Dollars", eg:
,"",
.. IF(ISNA(VLOOKUP(...Export_US_Dollars'!$A:$G,2,0)),"",
VLOOKUP(...Export_US_Dollars'!$A:$G,2,0)), ..

It was missing in your posted formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
Code Numpty said:
Sorry to post like this but I cannot see what is wrong with the following
nested VLOOKUP formula.

=IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)),
IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)),
IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)),
IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)),
IF(ISNA(VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)),
VLOOKUP($B$3,'C:\Calibration
Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0))

If the contents of B3 are on any sheet except
[Accounts.xls]Export_US_Dollars the formula returns the correct data. If B3
is on [Accounts.xls]Export_US_Dollars the formula returns FALSE.

The Export_US_Dollars worksheet has just been added, hence the change in
formula, it worked fine before. Can anyone point me in the right direction?
Click to expand...
Click to expand...
 
Back
Top