Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-02-2017, 11:24 AM   #1
RaunLGoode
Newly Registered User
 
Join Date: Feb 2004
Location: Austin, Texas, USA
Posts: 121
Thanks: 0
Thanked 0 Times in 0 Posts
RaunLGoode
LOOKUP against 2 named ranges

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.

RaunLGoode is offline   Reply With Quote
Old 05-02-2017, 12:11 PM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 72
Thanks: 0
Thanked 18 Times in 18 Posts
June7 is on a distinguished road
Re: LOOKUP against 2 named ranges

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.
June7 is offline   Reply With Quote
Old 05-09-2017, 11:26 AM   #3
NBVC
Only trying to help
 
NBVC's Avatar
 
Join Date: Apr 2008
Posts: 317
Thanks: 5
Thanked 51 Times in 50 Posts
NBVC will become famous soon enough NBVC will become famous soon enough
Re: LOOKUP against 2 named ranges

Try:

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

__________________
Microsoft MVP - Excel
(2010-2016)

Last edited by NBVC; 05-09-2017 at 12:19 PM.
NBVC is offline   Reply With Quote
Old 07-24-2017, 12:42 PM   #4
Kiwiman
Registered User
 
Kiwiman's Avatar
 
Join Date: Apr 2008
Location: Bath, UK
Posts: 799
Thanks: 8
Thanked 56 Times in 56 Posts
Kiwiman will become famous soon enough Kiwiman will become famous soon enough
Send a message via ICQ to Kiwiman
Re: LOOKUP against 2 named ranges

=iferror(vlookup(E2,range-a,5,0),iferror(vlookup(E2,range-b,3,0),""))

Sent from my SM-G900F using Tapatalk

__________________
HTH's
Take It Easy
Pete
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Man who keep feet firmly on ground have trouble putting on pants.
Kiwiman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
use list to create named ranges gerenrich Excel 2 01-06-2015 10:31 AM
Dynamic Named Ranges and Pivot Tables lobop3 Excel 2 11-29-2013 01:00 PM
Trouble with a formula and named ranges John64 Modules & VBA 12 08-26-2013 11:30 PM
Question Export to named range in excel with named ranges within the data merry_fay General 3 08-23-2011 04:16 PM
Export Code - Excel Named Ranges Trevor G Modules & VBA 2 02-25-2010 06:10 AM




All times are GMT -8. The time now is 05:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World