TWO VLOOKUPS in one argument not working 1

[max1x]

I'm using TWO VLOOKUPS to do some check and balances, across two worksheets via LOOKUP.

If #N/A is returned by VLOOKUP, it prints ITEM NOT LISTED, but does not do the 2nd VLOOKUP to get the value needed. The 2nd VLOOKUP works and reports value if listed by it's self.

=IF(ISNA(VLOOKUP(xx,refSheet!a:b,xx,False)),"ITEM NOT LISTED:",(VLOOKUP(xx,secRefSheet!a:b,xx,False)));

Any pointers.

 

[SkipVought]

hi,
[tt]
=IF(ISNA(VLOOKUP(xx,refSheet!a:b,xx,False)),if(isna(VLOOKUP(xx,secRefSheet!a:b,xx,False)),"ITEM NOT LISTED:",VLOOKUP(xx,secRefSheet!a:b,xx,False)),(VLOOKUP(xx,refSheet!a:b,xx,False)))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[max1x]

this is much nesting in there than I expected. It works great though.

Thank you.

Is there something else I can do to make it simpler, may be a different function?

 

[SkipVought]

Nope!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Only other thing that would make the formulas more readable might be using named ranges.

Call your refSheet!a:b range RefRng1
Call your secRefSheet!a:b range RefRng2

[tt]
=IF(ISNA(VLOOKUP(xx,RefRng1,xx,False)),if(isna(VLOOKUP(xx,RefRng2!a:b,xx,False)),"ITEM NOT LISTED:",VLOOKUP(xx,RefRng2!a:b,xx,False)),(VLOOKUP(xx,RefRng1!a:b,xx,False)))
[/tt]
I personally rather use INDEX and MATCH rather than VLOOKUP for several reasons...

1. with VLOOKUP, the lookup column must ALWAYS be in the left-most column range of the lookup.

2. the return column number is meaningless, and often difficult to understand.

So I Name EVERY column in my table using Create for selection in TOP row, where my column names are my range names. Actually, using the Excel 2007 TABLE feature, eliminates the need to name ranges, rather use the Table and Column heading names.

That way every lookup is as close to self-documenting as you can get...
[tt]
=INDEX(TheReturnColumnName,MATCH(LookupValue,TheLookupColumnName,0),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[max1x]

I'll definately try it out.

Thank you!