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 total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,834
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
35,834
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
35,834
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
35,834
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
35,834
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,089,901
Messages
5,411,110
Members
403,342
Latest member
faizanhamied

This Week's Hot Topics

Top