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)

1	2	3	4	5	6	7	8	9	10	11	12		8		Column	3
Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		=hlookup(N1,A1:L2,2,0)

<tbody>
</tbody>

 

[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