VLOOKUP column index

fenpark15

New Member
Joined
Oct 17, 2006
Messages
6
I need a formula to display a value from a data table based on two entered variables which represent a column and a row in the table. I'm trying to go about this with a nested VLOOKUP and HLOOKUP, but I don't know how to account for the column index. This is supposed to be a set integer value, but my needs call for the column index in the VLOOKUP to be based on the column found by HLOOKUP and vice versa. How can I accomplish this?

Thanks for your help!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
How about this?
=INDEX(TABLERANGE,ROWNUMBER,COLUMNNUMBER)
Where
TABLERANGE is the range of your data table
ROWNUMBER is the integer for the row number
COLUMNNUMBER is the integer for the row number

Regards,
 

fenpark15

New Member
Joined
Oct 17, 2006
Messages
6
I might not understand this response (I don't know Excel well at all), or I might not have been clear enough in my description of the problem.

Excel wants me to input an integer to represent the column index. My values are NOT integers, and the column I want to index is dependent on a user-defined variable. What I need is for something like HLOOKUP to find my desired decimal value at the top of the table, then output an integer that represents that column (i.e. A=1, B=2, etc.). Is this more clear?
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
If my understanding is correct, would this work?

=INDEX(TABLERANGE,ROWNUMBER,MATCH(LOOKUPVALUE,LOOKUPROW,0))

Where
• TABLERANGE is the range of your data table
• ROWNUMBER is the integer for the row number
• LOOKUPVALUE is the "decimal value" you're looking up
• LOOKUPROW is the row at the top of the table (TABLERANGE, I assume)

Regards,
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141

ADVERTISEMENT

Could you post part of your worksheet?

It sounds like you need to have column(ref) within your formula I'm not sure if Match would refer to the column position instead I think it will just refer to the relative position within your array which may represent a row position number
 

fenpark15

New Member
Joined
Oct 17, 2006
Messages
6
OK, I'm really close to solving this problem. I have an HLOOKUP function that is set to display a number that represents a column (B=2, C=3, etc.). I need simply need to nest this inside of a VLOOKUP so that this number will be used as the column index number. (VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
Here is what I have:
=VLOOKUP(c7,'TMR Data'!A1:I25,HLOOKUP(B4,'TMR Data'!A1:I2,2,FALSE),FALSE)

The HLOOKUP works fine on its own, and it should be seen as Excel as its output, which is just a number corresponding to a column. What could be wrong?
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
This looks like it should work, although make sure the answer to the HLOOKUP argument is a value (number) that is within the A:I index array!
Thus not greater than 9

That is all I can think off! I'm interested to find out the problem!

Good Luck
 

Forum statistics

Threads
1,141,680
Messages
5,707,789
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top