INDEX MATCH last match value in column

[umirin]

hi
I have this formula to find the first value , I'm looking to tweak it to find the last matched value

=INDEX(Data!$E$2:$G$20000,MATCH(Profit!A2,Data!$G$2:$G$20000),1)


I have tried the formula below which is adding the "2,1/" but excel adds an @ symbol to the Match DATA array which breaks the formula.

=INDEX(Data!$E$2:$G$20000,MATCH(2,1/(@Data!$G$2:$G$20000=Profit!A2)),1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[umirin]

Hello, using the latest Office 365 excel version

 

[Fluff]

Thanks for that, as you have 365 you can use Xmatch

=INDEX(Data!$E$2:$G$20000,XMATCH(Profit!A2,Data!$G$2:$G$20000,0,-1),1)

 

[umirin]

Thanks for that, as you have 365 you can use Xmatch

=INDEX(Data!$E$2:$G$20000,XMATCH(Profit!A2,Data!$G$2:$G$20000,0,-1),1)

Genius!! that fixed it, thanks heaps Fluff

 

[Fluff]

You're welcome & thanks for the feedback.