Lookup with variable column index number

[bradderzh]

Hey! Just registered to the site, but have been a long time lurker.

I was wondering if it is possible to use a lookup function (vlookup for instance) and force it to calculate the last populated cell in a row as the column index.

For instance:
[TABLE="width: 384"]
<tbody>[TR]
[TD="colspan: 2"]Unique Id[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1032[/TD]
[TD]Text[/TD]
[TD]Text[/TD]
[TD]Text[/TD]
[TD="align: right"]2695[/TD]
[TD="align: right"]0.0425[/TD]
[/TR]
[TR]
[TD]1150[/TD]
[TD]Text[/TD]
[TD="align: right"]4575[/TD]
[TD="align: right"]0.0722[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1151[/TD]
[TD]Text[/TD]
[TD]Text[/TD]
[TD="align: right"]266[/TD]
[TD="align: right"]0.0042[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1170[/TD]
[TD]Text[/TD]
[TD]Text[/TD]
[TD]Text[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0.0002[/TD]
[/TR]
[TR]
[TD]1174[/TD]
[TD]Text[/TD]
[TD]Text[/TD]
[TD]Text[/TD]
[TD="align: right"]0.0089[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Say I have this data set, is it possible create a formula that allows the lookup to find the last populated cell in the row and use that as the col_index_num?
I have been playing around max and column and managed to reference the column number of the last cell but cant seem to figure out how to use this in a lookup function.

Here is what I have so far:
=MAX(([the row selection]<>"")*(COLUMN([the row selection])))
It seems to work, but it relies on me manually selecting the row I want so wont work for a lookup function.

Thanks in advance!

 

[Aladin Akyurek]

Hey! Just registered to the site, but have been a long time lurker.

I was wondering if it is possible to use a lookup function (vlookup for instance) and force it to calculate the last populated cell in a row as the column index.

For instance:
[TABLE="width: 384"]
<TBODY>[TR]
[TD="colspan: 2"]Unique Id
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1032
[/TD]
[TD]Text
[/TD]
[TD]Text
[/TD]
[TD]Text
[/TD]
[TD="align: right"]2695
[/TD]
[TD="align: right"]0.0425
[/TD]
[/TR]
[TR]
[TD]1150
[/TD]
[TD]Text
[/TD]
[TD="align: right"]4575
[/TD]
[TD="align: right"]0.0722
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1151
[/TD]
[TD]Text
[/TD]
[TD]Text
[/TD]
[TD="align: right"]266
[/TD]
[TD="align: right"]0.0042
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1170
[/TD]
[TD]Text
[/TD]
[TD]Text
[/TD]
[TD]Text
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]0.0002
[/TD]
[/TR]
[TR]
[TD]1174
[/TD]
[TD]Text
[/TD]
[TD]Text
[/TD]
[TD]Text
[/TD]
[TD="align: right"]0.0089
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Say I have this data set, is it possible create a formula that allows the lookup to find the last populated cell in the row and use that as the col_index_num?
I have been playing around max and column and managed to reference the column number of the last cell but cant seem to figure out how to use this in a lookup function.

Here is what I have so far:
=MAX(([the row selection]<>"")*(COLUMN([the row selection])))
It seems to work, but it relies on me manually selecting the row I want so wont work for a lookup function.

Thanks in advance!

K2: 1170

L2, just enter:

=LOOKUP(9.99999999999999E+307,INDEX(A:F,MATCH(K2,A:A,0),0))

Is this what you are after?

 

[bradderzh]

Thanks for getting back to me so quickly!

This seems to work perfectly as far as I can tell. I haven't had much chance to dissect the formula (I am an excel noob really) but I am sure this will be easily applied to the purpose I have in mind. Thanks again!

 

[Aladin Akyurek]

Thanks for getting back to me so quickly!

You are welcome.

This seems to work perfectly as far as I can tell. I haven't had much chance to dissect the formula (I am an excel noob really) but I am sure this will be easily applied to the purpose I have in mind. Thanks again!

LOOKUP with Excel's big constant picks out with lightening speed the last number it comes across in a given reference.

The reference in your case is constructed with INDEX, delivering all of the cells in a row that MATCH specifies by matching the look up value in K2 against the values in column A.

 

[bradderzh]

Thanks for the quick explanation.

Haha I feel like I should be paying you! These response times are better than any help services my colleagues use when excel woes befall them! :P

One thing I will say is what is the use of the lookupvalue of '9.99999999999999E+307' What does that extremely large number mean in terms of the formula?

 

[Aladin Akyurek]

Thanks for the quick explanation.

Haha I feel like I should be paying you! These response times are better than any help services my colleagues use when excel woes befall them! :P

One thing I will say is what is the use of the lookupvalue of '9.99999999999999E+307' What does that extremely large number mean in terms of the formula?

See for more information:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html