# VLOOKUP column index

#### fenpark15

##### New Member
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
=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
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
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

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
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
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

Replies
7
Views
243
Replies
0
Views
403
Replies
1
Views
122
Replies
1
Views
344
Replies
21
Views
212

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?

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