Part of VLOOKUP not working

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

 

[Max]

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

 

[Pete_UK]

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

 

[Code Numpty]

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.

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

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?

 

[Max]

Welcome, glad that sorted it out for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik