find last row for specific text value in cell within tabel

[Morte1974]

Dear All,

I am new to this forum.
I am trying to find a solution for the following; within a tabel with several columns I am trying to find the last row (preferably column with the date for which the last specific value has been found in the table).
See attached example.
With vertical look-up I can't solve it and on the internet I only see VBA solution for which I have no experience.

Date Cat 1 Cat 2 Cat 3
1/1 Apple Apple Apple
1/2 Pear Nut Pear
1/3 Apple Nut Nut

The whole tabel I from Date (left top) to bottom right (nut)

Output I would like to have:
Apple 1/3
Nut 1/3
Pear 1/2

[Glenn Kennedy]

Use this array formula (with ; instead of , :

=MAX(IF($C$4:$D$10=H4,$B$4:$B$10))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

I also think there's a maxif in Excel 2016... check its syntax. Being in the Dark Ages of Excel 2013, I'm not familiar with it...

[Morte1974]

Hi Glenn, Great thanks for your help.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.