Not VBA - INDEX MATCH MAX DATE Question!!! :)

[Beto]

Alright, I am trying to bring back the most recent (highest DEX value) unit cost from a SQL table in Excel that matches an item number.




TRIMMEDITEMBR
DATERECD
UNITCOST

DEX_ROW_ID


393175722
8/7/2007 0:00
3.39

1


393175722
8/7/2007 0:00
3.47

2


393175622
8/7/2007 0:00
3.39

3


393175622
8/7/2007 0:00
3.63

4






=INDEX(_IV10200[UNITCOST],MATCH(393175722),_IV10200[TRIMMEDITEMBR],0)



I feel like i need another MATCH with a MAX, an array? tied into the INDEX?

The other issue is that in the TRIMMEDITEMBR there are also, of course multiple different item numbers. I would like to base the MAX value on the DEX ROW ID and match the item number and bring back the unit cost.

Does this make sense?

Thanks in advance! You guys are always super helpful!

-Beto

[georgiboy]

How about:

=INDEX($C$2:$C$6,MATCH(MAX(IF($A$2:$A$6=$A$10,$D$2:$D$6)),IF($A$2:$A$6=$A$1 0,$D$2:$D$6),0))
I have the item number to search in A10 and the above formula is in B10

You will need to hold CTRL & Shift and press Enter when you place the formula as it is an array formula

Hope this helps