How to use index and arrays for a nested query?

[annechez]

Hi,

I have a table as follows:

I am trying to return the latest date for a given Fruit and Type - can anyone help.

i.e. Pear with a Type of AI_646 should return a date of 10/10/2020.

Can anyone help.

I have to use my work version of Excel which is Office 2010, so I can't use the latest features.

Any guidance would be amazing, I'm going round in circles and never coming out with the correct answer!

Fruit	Type	Date
Apple	AI_840	10/03/2020​
Pear	AI_646	01/03/2020​
Pear	AI_840	12/12/2020​
Apple	AI_121	05/05/2020​
Orange	AI_123	06/06/2020​
Pear	AI_646	10/10/2020​
Pear	AI_646	07/09/2020​

 

[annechez]

I'm so close... but my data table sits on one worksheet and I want to do the calculation on another sheet....

I can't get it to work across sheets - do you need to do something special if your data is on a different sheet?

 

[annechez]

All working now.... no idea what I did!

 

[shaowu459]

Okay - I've managed to get to nearly work across sheets....
If I use this it works
=MAX(('H3G-Completed@190620'!B2:B4000="Pear")*('H3G-Completed@190620'!H2:H4000="AI_646")*'H3G-Completed@190620'!V2:V4000)

and if I use the Cell reference it doesn't work..
=MAX(('H3G-Completed@190620'!B2:B4000=B5)*('H3G-Completed@190620'!H2:H4000="AI_646")*'H3G-Completed@190620'!V2:V4000)5

It doesn't work when I replace the "Pear" with cell B5??

Double click to edit B5, i think there will be something invisible there, eg. blanks.

 

[annechez]

Got it....now.

If the search you are doing can't find a match, how can you get it to leave the cell blank rather than returning a zero (0)?

 

[shaowu459]

There are many ways, one way is right click the cell contains formula, format cells, in the first tab, maybe Number, click the last item, type 0;; in the textbox and click OK. I don't have computer around my hand, so above description may not so acurate.

 

[annechez]

okay - I'm making progress,

But when I use this:
=INDEX(D:D,MOD(MAX((A$2:A$8=F2)*(B$2:B$8=G2)*(C$2:C$8*10^4+ROW(2:8))),10^4))

If it can't find what I'm looking for, it returns the values that in Row 1 for column .

What error handling can I put in place to check if a match for F2 & G2 can't be found in the data, then I can do something else?

Help....