# Using a column number in a Hlookup formula

#### Ronanm

##### Board Regular
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Fluff

##### MrExcel MVP, Moderator
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

##### New Member
HLOOKUP(N1,offset(\$A\$1,0,0,Q1,1),2,0)

#### Fluff

##### MrExcel MVP, Moderator
For a Hlookup it should be
=HLOOKUP(N1,OFFSET(\$A\$1,0,0,2,Q1),2,0)

#### Ronanm

##### Board Regular
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

##### MrExcel MVP, Moderator
Have you tried what I suggested in post#4?

#### Ronanm

##### Board Regular
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

##### MrExcel MVP, Moderator
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.

Last edited:

#### Ronanm

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

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback