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
 

Fluff

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

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top