IFERROR(VLOOKUP()) with an IF(ISNA()) condition?

Hello Everyone,

A few days ago, I was recently working with a colleague and trying something in Excel to see if I could possibly combine an IFERROR(VLOOKUP()) formula with how I typically use IF(ISNA()). I do not have an exact this that I can upload, but I think I can easily explain it with some generic examples.

=IFERROR(VLOOKUP(F2,'Phase 2'!$A$2:$A$41,1,FALSE),VLOOKUP(F2,'Phase 3'!$A$2:$A$41,1,FALSE))

This is to find a value match from 2 different worksheets. However, instead of pulling in the value in those separate worksheets that I am looking up, I want to see if I could replace each of those VLOOKUPs to denote a specific value if it finds a match. When I usually use one VLOOKUP formula and want to accomplish this, I would do something like the below.

=IF(ISNA(VLOOKUP(F2,'Phase 2'!$A$2:$A$41,1,FALSE),"Phase 2 Not Found","Phase 2 Found")

My question is simply to see if it is possible to combine both of these approaches. I essentially want to use 2 VLOOKUPs in the first IFERROR(VLOOKUP(),VLOOKUP()) formula but want to include logic to essentially provide a simple return value if it finds a match from either lookup but each would need to return it's own value. So if I were to be trying this with the IFERROR example used above, it could return something like "Phase 2" from the first lookup and "Phase 3" from the second lookup.

Please let me know if you have any other questions. Again, my apologies for not having an exact example that I can upload here this time.

Try this:

=IF(ISNA(VLOOKUP(F2,'Phase 2'!$A$2:$A$41,1,FALSE),"Phase 2 Not Found",IF(ISNA(VLOOKUP(F2,'Phase 3'!$A$2:$A$41,1,FALSE),"Phase 3 Not Found","Both Phases Found"))

Can't test as you haven't provided a sample workbook.

If you just want to know if something is present (and not return a matching value), just use COUNTIF, not VLOOKUP.

You talk a lot about formulas, but you say little about what you ultimately want to achieve and leave me guessing.

Could you tell us exactly what you want to achieve in the different situations.
What is the desired result if there is a match in Phase2?
what is the desired result if there is a match in Phase3?
what is the desired result if there is no match in Phase2 and no match in Phase3?
what is the desired result (if that may occur) if there is a match in Phase2 and a match in Phase3? (If this is inpossible, please indicate this explicitly!)

Did you try my suggestion? Please respond to it. Thanks.

Try this

Formula:

Please Login or Register  to view this content.

Or, if you prefer,

Formula:

Please Login or Register  to view this content.

With a neater finish (especially if found in Phase 2 and Phase 3)

Formula:

Please Login or Register  to view this content.

If found in Phase 2 and found Phase 3 is not possible, this is sufficiënt:

Please try

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.

Note that post #10 is, I think, the only formula that copes with an ID appearing in both Phase 2 AND Phase 3, if that can happen.

Thanks for the nice feedback and rep .
Glad to have helped.

Yes, in Excel there are often different options to achieve the same thing, especially on this forum we learn a lot from each other.

Just for fun, another option with ISNA and MATCH, throwing in NOT and AND.

Formula:

Please Login or Register  to view this content.

Here's an updated file with all the solutions for comparison.