INDEX MATCH with 3 criteria (with a date range as criteria)

[LeopoldLeo]

Hello Everybody,
this is my first post, so don't be to harsh on me

Need you help with the following spreadsheet:

Column A: Name of supplier (A, B, C)
Column B: Product Description (X, Y, Z)
Column C: Price
Column D: Date when the quote was received.

Data goes like:
A X price date
A X price date
A X price date
A X price date
B X price date
B X price date
A Z price date
C X price date

I need INDEX match to return the value of the price by three criteria:
1. Supplier name must match
2. Product Description must match
3. The formula should pick up the latest date in the range where both criteria 1 & 2 are fulfilled! (that's the hardest part, since the column D is kilometer long and dates are repeating)

Currently im using following formula to match the first two criteria:
=INDEX(Price column,MATCH(Product description X & Supplier name A, All products column & All Supplier names column,0))

How to integrate the third "latest date" criteria? to get the most recent price?

Thank you in advance!

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe something like this (dates as dd/mm/yyyy)

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Supplier​	Description​	Price​	Date​		Supplier​	Description​	Price​
2​	A​	X​	10​	01/10/2014​		A​	X​	14​
3​	A​	X​	12​	21/10/2014​		B​	X​	18​
4​	A​	X​	14​	05/02/2015​
5​	A​	X​	16​	25/01/2015​
6​	B​	X​	18​	14/01/2015​
7​	B​	X​	20​	03/01/2015​
8​	A​	Z​	22​	23/01/2015​
9​	C​	X​	24​	12/12/2014​

Criteria in columns F:G

Formula in H2 copied down
=LOOKUP(1,0/FREQUENCY(0,1/(1+($A$2:$A$9=F2)*($B$2:$B$9=G2)*$D$2:$D$9)),$C$2:$C$9)

For an explanation how the formula works see
Return Entry Corresponding to Maximum Value Based on Conditions « EXCELXOR

Hope this helps

M.

 

[jarjarbingie]

=INDEX($C$2:$C$9,MATCH(supplier&$prod&MAX(IF(($A$2:$A$9=$A2)*($B$2:$B$9=$B2),$D$2:$D$9,"")),$A$2:$A$9&$B$2:$B$9&$D$2:$D$9,0))

press CTRL+SHIFT+ENTER..

Hello Everybody,
this is my first post, so don't be to harsh on me

Need you help with the following spreadsheet:

Column A: Name of supplier (A, B, C)
Column B: Product Description (X, Y, Z)
Column C: Price
Column D: Date when the quote was received.

Data goes like:
A X price date
A X price date
A X price date
A X price date
B X price date
B X price date
A Z price date
C X price date

I need INDEX match to return the value of the price by three criteria:
1. Supplier name must match
2. Product Description must match
3. The formula should pick up the latest date in the range where both criteria 1 & 2 are fulfilled! (that's the hardest part, since the column D is kilometer long and dates are repeating)

Currently im using following formula to match the first two criteria:
=INDEX(Price column,MATCH(Product description X & Supplier name A, All products column & All Supplier names column,0))

How to integrate the third "latest date" criteria? to get the most recent price?

Thank you in advance!

 

[LeopoldLeo]

Marcelo Branco, thank you very much for your input, have not used it yet, but will try later for sure!

Jarjarbingie, SALAMAT PO! That is what I needed intuitive formula! Used it and it worked perfectly for me!

You are great guys thank you!

 

[XOR LX]

@Marcelo

Thanks!

 

[tearsmith]

I have the exact same need as LeopoldLeo, except that my information is on Sheet 2 and needs to return the value on Sheet 1, cell C2.

Sheet 2
Column A: Truck Number (A, B, C)
Column B: License Plate (X, Y, Z)
Column E: Mileage
Column F: Date when the latest mileage was recorded.

On Sheet 1, Column A and B look like this:

UCI Truck #	License Plate
101	(NC) EA-5895

<colgroup><col><col></colgroup><tbody>
</tbody>

I need Column C, Cell C2, etc to INDEX match and return the value of the mileage in 'Sheet2"!E by three criteria:
1. Truck # must match
2. License Plate must match
3. The formula should pick up the latest date in the range where both criteria 1 & 2 are fulfilled!

I have used the formula:
=INDEX($C$2:$C$9,MATCH(supplier&$prod&MAX(IF(($A$2:$A$9=$A2)*($B$2:$B$9=$B2),$D$2:$D$9,"")),$A$2:$A$9&$B$2:$B$9&$D$2:$D$9,0))
as suggested by Jarjarbingie, changing the the cell references, but I get a #value! error.

I tried naming the blocks of information into arrays and using array names instead of sheet 2 cell references, but still the error.

Can someone please help?

Thank you, Tearsmith

 

[jarjarbingie]

make sure to press CTRL+SHIFT+ENTER instead of the regular ENTER..

I have the exact same need as LeopoldLeo, except that my information is on Sheet 2 and needs to return the value on Sheet 1, cell C2.

Sheet 2
Column A: Truck Number (A, B, C)
Column B: License Plate (X, Y, Z)
Column E: Mileage
Column F: Date when the latest mileage was recorded.

On Sheet 1, Column A and B look like this:

UCI Truck #	License Plate
101	(NC) EA-5895

<tbody>
</tbody>

I need Column C, Cell C2, etc to INDEX match and return the value of the mileage in 'Sheet2"!E by three criteria:
1. Truck # must match
2. License Plate must match
3. The formula should pick up the latest date in the range where both criteria 1 & 2 are fulfilled!

I have used the formula:
=INDEX($C$2:$C$9,MATCH(supplier&$prod&MAX(IF(($A$2:$A$9=$A2)*($B$2:$B$9=$B2),$D$2:$D$9,"")),$A$2:$A$9&$B$2:$B$9&$D$2:$D$9,0))
as suggested by Jarjarbingie, changing the the cell references, but I get a #value! error.

I tried naming the blocks of information into arrays and using array names instead of sheet 2 cell references, but still the error.

Can someone please help?

Thank you, Tearsmith

 

[AmjadM]

a #value! error caused me to change (correct) the original formula as follows;

=INDEX($C$2:$C$9,MATCH(F2&G2&MAX(IF(($A$2:$A$9=$F2)*($B$2:$B$9=$G2),$D$2:$D$9,"")),$A$2:$A$9&$B$2:$B$9&$D$2:$D$9,0))

And yes! make sure to press CTRL+SHIFT+ENTER instead of the regular ENTER.

After the above mentioned changes it worked nicely for me....

I am new here in this forum...
Can we attach excel files here in order to share our workings?