INDIRECT and VLOOKUP

C

Caliche

Active Member
Joined
Mar 26, 2002
Messages
339
I have (two) equal name named ranges, Sheet1!Tab1 and Sheet2!Tab1. In cell A1 I have a variable sheet name. I am trying to lookup with some value (A3) in Tab1 of sheet indicated by A1 value. My formula is:

VLOOKUP (A3, INDIRECT(A1 & “!Tab1”),4,0)

where A3 is my lookup value, but I am getting a #REF! error.

If I build my formula referencing directly the named range, it works nice, example VLOOKUP(A3,Sheet1!Tab1,4,0), which says to me that my problem is with INDIRECT function. All the examples I have looked at Mr. Excel board indicate that this must work, but I have not had success.

I have also tried VLOOKUP (A3, INDIRECT(“’” & A1 & “’!Tab1”),4,0) but not luck.

Thanks for your suggestions.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Hi,

In principle, your method should work:
Book1
ABCD
1IDFillFillNum
2a1
3c2
4b3
5
6
7LookupSheet1
8Valuec
9Result2
Sheet1



...have you confirmed that your ranges are themselves set up right? What do they refer to?
 
Upvote 0
Yes, Paddy. In fact, when I try VLOOKUP using directly the sheet name (i.e. VLOOKUP(A3,Sheet2!Tab1,4,0) or VLOOKUP(A3,Sheet2!Tab1,4,0) things work nice.

Let me to say that my named ranges are defined using OFFSET function. Does it care ?.

Example : Sheet1!Tab1 "Refers to"

OFFSET($B$2,0,0,COUNTA($B$2:$B$65536),5). ($b$2 and $b$65536 in Sheet1).

Similar way for Sheet2!Tab1.

Thanks a lot for your help.
 
Upvote 0
Caliche said:
...Let me to say that my named ranges are defined using OFFSET function. Does it care ?.

Example : Sheet1!Tab1 "Refers to"

OFFSET($B$2,0,0,COUNTA($B$2:$B$65536),5). ($b$2 and $b$65536 in Sheet1).

Similar way for Sheet2!Tab1...
Click to expand...

Yes, it does care. Definition by means of OFFSET creates an array object. INDIRECT does not accept array objects, only range objects.

Use EVAL instead of INDIRECT.

EVAL is available in the morefunc.xll add-in.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
2D spilled array, using REDUCE and INDIRECT to consolidate data from multiple sheets.
Replies
5
Views
309
Fluff
Fluff
T
  • Question
Passing name of array to function based on variable
Replies
5
Views
383
TheRedCardinal
T
I
  • Solved
Indirect Formula help
Replies
7
Views
174
ItalianPlatinum
I
T
  • Question
VLOOKUP + INDIRECT overlap (maybe not possible)
Replies
5
Views
234
TXNCPO
T
B
  • Question
Getting error if linked cell row deleted in different sheet of large data.
Replies
3
Views
131
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,493
Messages
6,125,131
Members
449,206
Latest member
burgsrus

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top