Look up last entry with specific header.

[jtakw]

Hi all,

Haven't worked on Excel for over a year and have gotten rusty, if someone may help me with a formula, I'd really appreciate it.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Apples	Oranges	Pears	Bananas	Grapes	Apples	Oranges	Pears	Bananas	Grapes	Apples	Oranges	Pears	Bananas	Grapes
2	100	200	150		300	50	75	125	199	300	250	20	80		150

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Using the above sample, what I need is a formula that will give me the most recent entry under the Category "Bananas", in this case, the result should be "199".
This is a stripped down and simplified version of my actual data, and is a monthly growing list, so the formula should always give me the most recent entry as the list grows.

Thank you very much in advance.

 

[Marcelo Branco]

Is the most recent the first non-zero (non-blank) from left to right or from right to left?

M.

 

[jtakw]

From left to right, the list will grow monthly with more columns added (i.e. P, Q, R, S, etc.) with the same header repetition.
Please let me know if that clarifies your question.

Thank you.

 

[Aladin Akyurek]

Control+shift+enter. not just enter:

=LOOKUP(9.99999999999999E+307,SEARCH("bananas",IF(ISNUMBER(A2:O2),A1:O1)),A2:O2)

 

[jtakw]

Thank you Aladin, works perfect.

I originally was trying to get it to work with Sumproduct and Lookup, but couldn't get it work; your array formula works perfect, just to satisfy my curiosity, could it have worked with Sumproduct and Lookup?

Thank you Marcelo also.

 

[Aladin Akyurek]

Thank you Aladin, works perfect.

Glad to help.

I originally was trying to get it to work with Sumproduct and Lookup, but couldn't get it work; your array formula works perfect, just to satisfy my curiosity, could it have worked with Sumproduct and Lookup? [...]

The task here requires a retrieval formula. While LOOKUP is a retrieval function, SUMPRODUCT is not. Also, the regular use of LOOKUP requires an array/a range sorted in ascending order. So the regular usage won't do combined with SUMPRODUCT which does pairwise multiplications whose results are summed. Not a desirable combination. The short answer is thus no.[/QUOTE]

 

[jtakw]

Thank you for your explanation, it makes sense why I wasn't getting the correct results with Sumproduct/Lookup.

Thanks again.