Forum Discussion

Venkatesh Vundela's avatar
Venkatesh Vundela
Copper Contributor
Jan 09, 2018
Solved

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 th...
Formulas & Functions
Show and Tell
  • Mark Fitzgerald's avatar
    Mark Fitzgerald
    Jan 09, 2018

    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)
Mark Fitzgerald's avatar
Mark Fitzgerald
Iron Contributor
Jan 09, 2018

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)
  • Venkatesh Vundela's avatar
    Venkatesh Vundela
    Copper Contributor
    Jan 09, 2018

    Thank You so much
    That worked like a charm