Hlookup - use indirect for Row index number

[nadjalil]

Dear All
i have a problem to bring row index number from a different cell by indirect formula in Hlookup.
attached is an example of my file...

thanks to help me on this...

Kind Regards

 

[Nebu]

Hi:

Why you want indirect for this can't you give an absolute cell reference ?

Thanks

 

[Ramesh]

Not sure, why you are trying to use an indirect function here..

The 3rd parameter of the HLookup function can be replaced with the cell address where you have used the indirect function.

Your current formula :

=HLOOKUP(B2,Details!$E$14:$E$28,INDIRECT(Sheet2!$A$3,0),FALSE)

Formula can be written as:

=HLOOKUP($B$2,Details!$E$14:$E$25,$A$3,0)

In the above formula, the cell $A$3 has been given as the 3rd parameter for HLookup function.

Please let me know, if I understood your requirement in a wrong way

 

[Khalid NGO]

Hi,
What is your desire output?

Regards,

Edit:
Didn't noticed Nebu & Ramesh already replied.

 

[nadjalil]

Hi Nebu
my actual file has 1000 row index numbers and if i can divert it through a cell where i can give cell no i can save a lot of time to change almost 2000 formulas. i dont mind if you can give some thing simpler...

thanks for your help

 

[Nebu]

Hi:

I understand that, do not wrap your cell a3 with indirect function you do not need indirect function to make your lookup dynamic just give a3 your Hlookup will work. I guess Ramesh has already explained it in one of the earlier post.

Thanks

 

[nadjalil]

Not sure, why you are trying to use an indirect function here..

The 3rd parameter of the HLookup function can be replaced with the cell address where you have used the indirect function.

Your current formula :

=HLOOKUP(B2,Details!$E$14:$E$28,INDIRECT(Sheet2!$A$3,0),FALSE)

Formula can be written as:

=HLOOKUP($B$2,Details!$E$14:$E$25,$A$3,0)

In the above formula, the cell $A$3 has been given as the 3rd parameter for HLookup function.

Please let me know, if I understood your requirement in a wrong way

Thanks Boss you understood my file and requirement correctily....
thanks for the inputs

 

[nadjalil]

thanks Nebu
for your time i got you both correctly and it's working fine in file...

 

[Nebu]

Hi:

You can explore index match rather than H lookup it is far more flexible and robust than H lookup or V lookup, refer the following link.
http://www.exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm

Thanks