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:

Unique Id
1032	Text	Text	Text	2695	0.0425
1150	Text	4575	0.0722
1151	Text	Text	266	0.0042
1170	Text	Text	Text	14	0.0002
1174	Text	Text	Text	0.0089

<tbody>
</tbody>

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:

Unique Id
1032	Text	Text	Text	2695	0.0425
1150	Text	4575	0.0722
1151	Text	Text	266	0.0042
1170	Text	Text	Text	14	0.0002
1174	Text	Text	Text	0.0089

<TBODY>
</TBODY>

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