combining index + array to return values

[cnoh]

Hi. I have a schedule in table format showing the fruit that each team member is responsible for bringing on a certain date. I'd like to return a list that indicates:

1. Who is bringing which fruit on which date, e.g., Beth oranges Friday, April 11, Joyce raspberries Thursday, April 10

2. The last date that each fruit is being brought, e.g., apples on Thursday, April 17, bananas on Monday, April 7, and so on. For this one, I have figured out how to determine the name of the last person responsible for a fruit e.g., =ArrayFormula(LOOKUP(2,1/(B5:B36<>""),B5:B36)), but I also want to know which date they are assigned. It seems that I could someone combine the array with index, but I'm stuck.

Any tips would be greatly appreciated!

 

[Fluff]

Is this for Excel or Google Sheets?

 

[cnoh]

Either, really. I use both.

 

[Fluff]

OK, but if you need something to work in Sheets you need to say that & such questions should be posted in the General Discussion & Other Applications section.

For you 2nd question change the 2nd B3:B36 to A5:A36 to return the date.

 

[Fluff]

For your 1st question, how about

=LET(d,B5:G34,HSTACK(TOCOL(IF(d<>"",A5:A34,1/0),2),TOCOL(IF(d<>"",B3:G3,1/0),2),TOCOL(d,1)))

 

[cnoh]

OK, but if you need something to work in Sheets you need to say that & such questions should be posted in the General Discussion & Other Applications section.

For you 2nd question change the 2nd B3:B36 to A5:A36 to return the date.

Understood, thank you for the feedback and reminder.

And re: 2nd question, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.