LOOKUP against 2 named ranges (1 Viewer)

RaunLGoode

Registered User.
Local time
Today, 17:23
Joined
Feb 18, 2004
Messages
122
I am using VLOOKUP to find a value in a named range(Range-A). I am using IFNA to trap values the Return "N/A" when no match is found. I then create a VLOOKUP to look in a different table (Range-B) for a match.
=IF(ISNA(VLOOKUP(E2,Range-A,5,FALSE)), "", VLOOKUP(E2,Range-A,5,FALSE))
and
=IF(ISNA(VLOOKUP(E2,Range-B,3,FALSE)), "", VLOOKUP(E2,Range-B,3,FALSE))

I would like to combine these 2 steps into a single process to Search Range-A for a value, and if not found, Search Range-B for the value. Ideally If the value isn't found in either range, I'd like to use IFNA to trap that condition.
I have looked in this and other forums and haven't found a solution. I'd really appreciate some help, if anyone has done this before.
 

June7

AWF VIP
Local time
Today, 14:23
Joined
Mar 9, 2014
Messages
5,423
Asking an Excel question in an Access forum?

Why does it need to be all in one cell? Do the calcs in individual cells then in a third cell do an If() to select result. The first 2 columns can be hidden.
 

NBVC

Only trying to help
Local time
Today, 18:23
Joined
Apr 25, 2008
Messages
317
Try:

=IFNA(VLOOKUP(E2,Range-A,5,FALSE),IFNA(VLOOKUP(E2,Range-B,3,FALSE),""))
 
Last edited:

Kiwiman

Registered User
Local time
Today, 22:23
Joined
Apr 27, 2008
Messages
799
=iferror(vlookup(E2,range-a,5,0),iferror(vlookup(E2,range-b,3,0),""))

Sent from my SM-G900F using Tapatalk
 

Users who are viewing this thread

Top Bottom