Trying to extract value from table with Large() being one of the conditions.

I have a table with names in A, dates in B and text in C.
I'm looking for a formula (prefer not a UDF()) that will pull column C for the latest date for a name.

Example (this is on sheet 2)

Please Login or Register  to view this content.

I want to find what did Bill do last? Answer he Tallied. put onto sheet1 next to Bill's name
I also need to know the same thing for Dave, and everyone else in the list.

I can find the latest day for everyone by using the array formula ={LARGE(IF(sheet2!A$1:A$518=sheet1!$A1,sheet2!$B$1:$B$518),1)} or something similar, but I can't figure out how to then get the action from column C.

Is the data sorted by Name, Date as implied by source data ?

Yes
-use LOOKUP (ie find last instance of given name and retrieve appropriate value)

Please Login or Register  to view this content.

No
- use INDEX with MATCH - if we assume the date is stored in sheet1!$B1 etc...

Please Login or Register  to view this content.

Hi DonkeyOte;
The table is not sorted in any way. I thought there would be an array formula that would find this for me.

Originally Posted by foxguy

The table is not sorted in any way. I thought there would be an array formula that would find this for me.

In which case refer to the 2nd example provided (ie for No - data is unsorted)
The embedded INDEX is used to avoid need for CSE entry, however, it will be no more efficient so use an Array if you prefer.

Originally Posted by DonkeyOte

In which case refer to the 2nd example provided (ie for No - data is unsorted)
The embedded INDEX is used to avoid need for CSE entry, however, it will be no more efficient so use an Array if you prefer.

Sorry, didn't even notice that there were 2 examples. Just saw the 1st line asking if it was sorted and stopped reading.

Originally Posted by foxguy

Just saw the 1st line asking if it was sorted and stopped reading.

reassuring...

Originally Posted by DonkeyOte

reassuring...

Well, I didn't completely stop reading. I read far enough to see that you were using Lookup and because I knew that the table wasn't sorted. I combined that with the question about being sorted and knew it wouldn't work. That's where I stopped reading. I look at it now, and realize that there are 2 code windows. Have no idea why that didn't ring a bell and tell me that there were 2 different codes. I think I was just in a hurry. We had a power outage for over 24 hours, and I'm rushing to catch up with all the messages from people I'm working with.

Hi DonkeyOte;

Finally got around to looking at your 2nd formula in message #2. There is no date on Sheet1. That's part of what I need to find. It's just a middle step to finding the corresponding action, so it can be thrown away after finding it.
I have thought more about it, and I really don't want an array formula after all. Someone else will be using the workbook, and I don't want to have to rely on them to enter an array for a new row.

So it sounds like I either have to add a column or use a UDF().

Originally Posted by foxguy

I have a table with names in A, dates in B and text in C.
I'm looking for a formula (prefer not a UDF()) that will pull column C for the latest date for a name...

...I can find the latest day for everyone by using the array formula...

Originally Posted by foxguy

There is no date on Sheet1. That's part of what I need to find.

You stipulate in post #1 you have the date via the Array, no ?

My example simply assumed you would be storing this value in a cell adjacent to the name on Sheet1 - in my example I presumed this to be B1 where name was stored in A1.

So ... I confess at this juncture I can't really fathom the issue / question I'm afraid...

edit:

Originally Posted by foxguy

It's [date] just a middle step to finding the corresponding action, so it can be thrown away after finding it.

FWIW, IMO, you should store the Date value in a separate cell so as to avoid needless / expensive repetitive calculations required for a single cell calc.

Hi DonkeyOte;

You're right. The way I worded it did give the impression that I had the date.
I should have said I wanted to gather the action for the latest action in only 1 formula. I know how to get the latest date. I just didn't know how to then get the action in the same formula.

My Bad.

But since then, I've decided that I really don't want an array formula after all.