Using a column number in a Hlookup formula

[Ronanm]

Hi

I have a problem trying to use a column number in the formula below.

HLOOKUP(N1,A1:A&INDIRECT(Q1),2)

There is a column number that will appear in cell Q1 and I want the lookup range to work based on that, for example if there was an 8 in cell Q1, then Excel would read the Hlookup as

HLOOKUP(N1,A1:A8),2)

Thanks

 

[Fluff]

Your trying to use the value in Q1 as a row number not a column number.
Do you mean it should be A1:H2

 

[KUYJS]

HLOOKUP(N1,offset($A$1,0,0,Q1,1),2,0)

 

[Fluff]

For a Hlookup it should be
=HLOOKUP(N1,OFFSET($A$1,0,0,2,Q1),2,0)

 

[Ronanm]

Actually, sorry, I was wrong Fluff, it should have been HLOOKUP(N1,A1:H2,2,0), where Q1 is the column number e.g. 8
So I'd want HLOOKUP(N1,A1:"Column Num ber as a letter"&2,2,0)

If that makes sence.

Thanks

 

[Fluff]

Have you tried what I suggested in post#4?

 

[Ronanm]

Hi
I did but it didn't work. When I steped through it with evaluate it was showing the range Hlookup(8,$A$1:$C$2,2,0)
[TABLE="width: 1088"]
<tbody>[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]Column[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD][/TD]
[TD="colspan: 3"]=hlookup(N1,A1:L2,2,0)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

That's because you have a 3 (in what I presume is Q1) which equates to col C.
So the formula is doing what you asked for.

 

[Ronanm]

Sorry Fluff. Spot on mate. I confused myself with the cells I was looking up! Works a treat thanks. Appreciate it.

 

[Fluff]

Glad to help & thanks for the feedback