SOLVED
Home

Having an issue with HLOOKUP

Venkatesh Vundela
New Contributor

Having an issue with HLOOKUP

I have created so many tables giving names as TABLE1, TABLE2,.............TABLE7.

Attached is a snapshot of it

Now if I change the value in grey area then HLOOKUP should automatically refer to that numbered table as array. I can do this using if condition but I have actual sheet where there 100+ tables so I want it to be dynamic.


Any help is appreciable

Thanks

2 Replies
Solution

Re: Having an issue with HLOOKUP

I have to assume that TABLE1 is a named range and not an Excel table (i.e. ListBox).  Were it an Excel table the reference as shown in H4 would have to be Table1[#All].

 

The INDIRECT function returns a reference to a cell, named range or object based on a string or cell reference input.  You were almost there with the formula shown in H5.  You just need to wrap your reference in an INDIRECT function:

=HLOOKUP(G3,INDIRECT("TABLE"&G2),2,FALSE)
Highlighted

Re: Having an issue with HLOOKUP

Thank You so much
That worked like a charm

Related Conversations
OCM encountered an error. Won't start.
Brett Stacey in Outlook Customer Manager on
3 Replies
IFERROR - HLOOKUP- IF formula error
data24365 in Formulas and Functions on
14 Replies
Availability
Yinghua Zeng in System Center AMA on
6 Replies
High DPI
Eric Pelezo in Surface Book on
5 Replies
Excel
Tory Guidry in Excel on
2 Replies