Look up last entry with specific header.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
1ApplesOrangesPearsBananasGrapesApplesOrangesPearsBananasGrapesApplesOrangesPearsBananasGrapes
210020015030050751251993002502080150

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,859
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Is the most recent the first non-zero (non-blank) from left to right or from right to left?

M.
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Control+shift+enter. not just enter:

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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]
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Thank you for your explanation, it makes sense why I wasn't getting the correct results with Sumproduct/Lookup.

Thanks again.
 

Forum statistics

Threads
1,141,299
Messages
5,705,573
Members
421,399
Latest member
hjweiss00

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?

Disable AdBlock

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
Top