Jan 09 2018
11:02 AM
- last edited on
Jul 25 2018
10:43 AM
by
TechCommunityAP
Jan 09 2018
11:02 AM
- last edited on
Jul 25 2018
10:43 AM
by
TechCommunityAP
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
Jan 09 2018 11:16 PM
SolutionI 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)
Jan 09 2018 11:16 PM
SolutionI 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)