Vlookup - editable index

[MarkCBB]

Hi there,

This question is more out of curiosity than necessity. Does anyone know of a Function that has been made that adds an argument to the vlookup Function that allows for the index column to be choosen as the result column is choosen. i.e.

=vlookup(A1,2,C:F,4,0)

the 2 is the index column = Column "D" and the result column is "F".

 

[Andrew Poulsom]

VLOOKUP looks in the leftmost column of lookup range. You could use INDEX or OFFSET to construct that argument. What are you trying to do exactly?

 

[MarkCBB]

Hi Andrew,

Example: I have a datatable ("Table1") that has a few index columns in it. i.e. Product, sales code. Depending on the infoamtion i have to find the relevete corisponding infomation, I would use either one, the one formula would use Table1 as the table the ither would use B:G as the table.

More interested as to if there is a custom function that has been made that can do this.

How would you approach this?

 

[Andrew Poulsom]

If J1 contains the starting column number you can construct a reference that starts at that column with:

OFFSET(Table1,0,J1-1,,COLUMNS(Table1)-J1+1)

 

[MarkCBB]

Curiosity has now increase, It seems that this should be a function in Excel, would really be helpful.

I used your Formula above and ended up with this:

Named: "TableRange"
=OFFSET(Sheet1!$A$1,0,ColumnName,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)-ColumnName)

"Column Range"
=MATCH(Sheet2!$A$1,Sheet1!$A$1:$G$1,0)-1

Sheet2 "A1" would be the name of the Column. Sheet2 "B1" would be the lookup_Value. Sheet2 "C1" =VLOOKUP(B1,TableRange,2,0)

Just curious to how much work it would take to create this as a function. So that Sheet2 "A1" is not needed to create the column Index Number, but would be an Argument in the Vlookup.

Just seems that this would be pretty cool. But the above works perfectly as well.

Thank you for your assistance.

Regards,
Mark Blackburn

 

[Andrew Poulsom]

I was thinking you could use OFFSET inside the VLOOKUP formula to return the lookup range, not to define a name.

 

[MarkCBB]

O yes, of course, Thanks Andrew.