Part of VLOOKUP not working
 

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?

Part of VLOOKUP not working
 

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" wrote:
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?

Part of VLOOKUP not working
 

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

On Dec 10, 1:00*pm, Code Numpty
wrote:
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?

Part of VLOOKUP not working
 

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" wrote:

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" wrote:
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?

Part of VLOOKUP not working
 

Welcome, glad that sorted it out for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Code Numpty" wrote in message
...
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.