Using a column number in a Hlookup formula

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
Your trying to use the value in Q1 as a row number not a column number.
Do you mean it should be A1:H2
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
For a Hlookup it should be
=HLOOKUP(N1,OFFSET($A$1,0,0,2,Q1),2,0)
 

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
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
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
Have you tried what I suggested in post#4?
 

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
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
234567891011128Column3
JanFebMarAprMayJunJulAugSepOctNovDec=hlookup(N1,A1:L2,2,0)

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
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
Joined
Nov 13, 2010
Messages
107
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
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,496
Messages
5,414,887
Members
403,550
Latest member
Haima1

This Week's Hot Topics

Top