ISNA with VLOOKUP against 2 locations

[steph]

I have a new problem I need help with. I am trying to look up a value found
in workbook #1. I need to lookup the value in workbook #2 and, if it's not
found there, then lookup the value in a different tab of workbook #1. Here
is what I have so far.

equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,5,FALSE))

The 2nd part of the formula works fine as the lookup returns the appropriate
P8 value from the tab in workbook 1 when it is not found in workbook2.
However, when the value IS found in workbook2, it returns a value of FALSE,
not the appropriate lookup value.

Should I be using a different IS function? Any assistance would be greatly
appreciated!!

 

[T. Valko]

Try this:

=lIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0)),VLOOKUP(P8,'tabname
in workbook1'!$A:$F,5,0),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0))

 

[Pete_UK]

The logic of your formula is "If there is an error looking in book2,
then look in book1". You don't say to look in book 2 if there is no
error, so you might like to amend your formula to:

=IF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,
17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,
5,FALSE),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE))

However, this does not check for the item being in book1 - it assumes
it is there, and if not the formula will return #N/A.

A more generic approach would be:

=IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not
present",vlookup_2),vlookup_1)

Looks in table_1 first and returns from there if present, otherwise
looks in table_2 and returns from there if present. If absent from
both, then the error message is returned.

Hope this helps.

Pete

 

[steph]

Yes, this worked perfectly--thanks very much!!
--
Thanks so much!

Try this:

=lIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0)),VLOOKUP(P8,'tabname
in workbook1'!$A:$F,5,0),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0))


--
Biff
Microsoft Excel MVP

I have a new problem I need help with. I am trying to look up a value
found
in workbook #1. I need to lookup the value in workbook #2 and, if it's
not
found there, then lookup the value in a different tab of workbook #1.
Here
is what I have so far.

equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P8,'tabname
in workbook1'!$A:$F,5,FALSE))

The 2nd part of the formula works fine as the lookup returns the
appropriate
P8 value from the tab in workbook 1 when it is not found in workbook2.
However, when the value IS found in workbook2, it returns a value of
FALSE,
not the appropriate lookup value.

Should I be using a different IS function? Any assistance would be
greatly
appreciated!!

 

[steph]

Pete, this worked perfectly. I couldn't get your 2nd suggestion to work but
your 1st suggestion matched T. Valko's (I'm OK with getting #N/A so I can
manually add those values to one of my workbooks). The only difference
between your suggestiong and T's was T used 0 where you used FALSE. Can you
tell me the difference?
--
Thanks so much!

The logic of your formula is "If there is an error looking in book2,
then look in book1". You don't say to look in book 2 if there is no
error, so you might like to amend your formula to:

=IF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,
17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,
5,FALSE),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE))

However, this does not check for the item being in book1 - it assumes
it is there, and if not the formula will return #N/A.

A more generic approach would be:

=IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not
present",vlookup_2),vlookup_1)

Looks in table_1 first and returns from there if present, otherwise
looks in table_2 and returns from there if present. If absent from
both, then the error message is returned.

Hope this helps.

Pete

I have a new problem I need help with. I am trying to look up a value found
in workbook #1. I need to lookup the value in workbook #2 and, if it's not
found there, then lookup the value in a different tab of workbook #1. Here
is what I have so far.

equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P­8,'tabname in workbook1'!$A:$F,5,FALSE))

The 2nd part of the formula works fine as the lookup returns the appropriate
P8 value from the tab in workbook 1 when it is not found in workbook2.
However, when the value IS found in workbook2, it returns a value of FALSE,
not the appropriate lookup value.

Should I be using a different IS function? Any assistance would be greatly
appreciated!!

 

[Pete_UK]

They are both the same - I usually use 0 rather than FALSE as it is
less typing, but in this case I just copied your formula as you had
presented it.

Thanks for feeding back.

Pete

Pete, this worked perfectly.  I couldn't get your 2nd suggestion to workbut
your 1st suggestion matched T. Valko's (I'm OK with getting #N/A so I can
manually add those values to one of my workbooks).  The only difference
between your suggestiong and T's was T used 0 where you used FALSE.  Canyou
tell me the difference?
--
Thanks so much!

The logic of your formula is "If there is an error looking in book2,
then look in book1". You don't say to look in book 2 if there is no
error, so you might like to amend your formula to:

=IF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,
17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,
5,FALSE),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE))

However, this does not check for the item being in book1 - it assumes
it is there, and if not the formula will return #N/A.

A more generic approach would be:

Looks in table_1 first and returns from there if present, otherwise
looks in table_2 and returns from there if present. If absent from
both, then the error message is returned.

Hope this helps.

Pete

I have a new problem I need help with.  I am trying to look up a value found
in workbook #1.  I need to lookup the value in workbook #2 and, if it's not
found there, then lookup the value in a different tab of workbook #1.  Here
is what I have so far.
equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P­­8,'tabname in workbook1'!$A:$F,5,FALSE))
The 2nd part of the formula works fine as the lookup returns the appropriate
P8 value from the tab in workbook 1 when it is not found in workbook2. 
However, when the value IS found in workbook2, it returns a value of FALSE,
not the appropriate lookup value.
Should I be using a different IS function?  Any assistance would be greatly
appreciated!!

- Show quoted text -

 

[T. Valko]

T used 0 where you used FALSE.

Can you tell me the difference?

Saves a few keystrokes!

0 = FALSE
1 = TRUE

--
Biff
Microsoft Excel MVP

Pete, this worked perfectly. I couldn't get your 2nd suggestion to work
but
your 1st suggestion matched T. Valko's (I'm OK with getting #N/A so I can
manually add those values to one of my workbooks). The only difference
between your suggestiong and T's was T used 0 where you used FALSE. Can
you
tell me the difference?
--
Thanks so much!

The logic of your formula is "If there is an error looking in book2,
then look in book1". You don't say to look in book 2 if there is no
error, so you might like to amend your formula to:

=IF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,
17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,
5,FALSE),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE))

However, this does not check for the item being in book1 - it assumes
it is there, and if not the formula will return #N/A.

A more generic approach would be:

=IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not
present",vlookup_2),vlookup_1)

Looks in table_1 first and returns from there if present, otherwise
looks in table_2 and returns from there if present. If absent from
both, then the error message is returned.

Hope this helps.

Pete

I have a new problem I need help with. I am trying to look up a value
found
in workbook #1. I need to lookup the value in workbook #2 and, if it's
not
found there, then lookup the value in a different tab of workbook #1.
Here
is what I have so far.

equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P­8,'tabname
in workbook1'!$A:$F,5,FALSE))

The 2nd part of the formula works fine as the lookup returns the
appropriate
P8 value from the tab in workbook 1 when it is not found in workbook2.
However, when the value IS found in workbook2, it returns a value of
FALSE,
not the appropriate lookup value.

Should I be using a different IS function? Any assistance would be
greatly
appreciated!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Yes, this worked perfectly--thanks very much!!
--
Thanks so much!

Try this:

=lIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0)),VLOOKUP(P8,'tabname
in workbook1'!$A:$F,5,0),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0))


--
Biff
Microsoft Excel MVP

I have a new problem I need help with. I am trying to look up a value
found
in workbook #1. I need to lookup the value in workbook #2 and, if it's
not
found there, then lookup the value in a different tab of workbook #1.
Here
is what I have so far.

equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P8,'tabname
in workbook1'!$A:$F,5,FALSE))

The 2nd part of the formula works fine as the lookup returns the
appropriate
P8 value from the tab in workbook 1 when it is not found in workbook2.
However, when the value IS found in workbook2, it returns a value of
FALSE,
not the appropriate lookup value.

Should I be using a different IS function? Any assistance would be
greatly
appreciated!!