I am trying to run a VLookup across 19 tabs. The formula and result are below
=Vlookup(2255099,INDIRECT("'"&CENTERS&"!A5:R395""),13,0) returns a #VALUE
When I evaluate the formula, the result is:
=Vlookup(2255099,INDIRECT("'"&#VALUE&"!A5:R395""),13,0)
Does the #VALUE where CENTER was mean there is a problem with my Named Range?
Thanks.
Hi Renleff,
INDIRECT and Named Range are two very different things.
If CENTERS is a Named Range then your VLOOKUP formulas should look like this
=VLOOKUP(2255099,CENTERS,13,0)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
How is CENTERS defined?
If CENTERS spans more than one sheet it won't work.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi AlKey,
When I tried your formula it returned a #NA. I may certainly be misguided but the reason I used the INDIRECT is that I want the formula to look at each tab within Named Range and then look for the specific criteria within the range I listed A5:R395, column 13.
Thanks.
Renleff
Tony,
Centers is a Named Range comprising 19 tabs and this is why I thought I needed to us the Indirect function.
Thanks
Renleff
What type of data is the formula supposed to return? Is it text? Numeric? Could be either? Something else?
What are the sheet names? Are they random names or do they follow some sequential naming pattern?
Will the lookup value always be found on one of the sheets?
Is this a duplicate thread:
http://www.excelforum.com/excel-form...med-range.html
The same formulas in the first post, I would say yes.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
Welcome to the Forum, unfortunately:
This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.
Thread Closed.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks