3 value lookup

[Johnboy28]

Hi, I require a formula to match Day, SO Number & product Code to return invoiced Weight?

Thank you

Day	SO Number	Product Code	Invoiced Weight		Day	SO Number	Product Code	Invoiced Weight
29/01/2019	SO-291420	26102	19040		30/01/2019	SO-293830	20502	??
30/01/2019	SO-292253	10400	168		30/01/2019	SO-293830	10519	??
30/01/2019	SO-292253	10401	168		30/01/2019	SO-293917	20700	??
30/01/2019	SO-293830	20502	117
30/01/2019	SO-293830	21617	698
30/01/2019	SO-293830	21618	151
30/01/2019	SO-293830	82100	1012
30/01/2019	SO-293830	10331	190
30/01/2019	SO-293830	10519	45
30/01/2019	SO-293917	20039	120
30/01/2019	SO-293917	20700	115

<tbody>
</tbody>

​

 

[Johnboy28]

Here is one option:

=LOOKUP(2,1/(($A$2:$A$12=F2)*($B$2:$B$12=G2)*($C$2:$C$12=H2)),$D$2:$D$12)

Thanks for your help. Formula works great.

 

[tyija1995]

Exactly. That may or may not be what is required. Sometimes you may need the first 'hit' from the top (use index match), sometimes the last (use lookup) and sometimes a sum (sumproduct or sumifs).

I knew about the index match returning the first instance, but did not know that lookup returned the last! That's good to know.

I suppose there would be issues if there were more than 2 cases of uniqueness and you need one of the middle cases? Then more criteria would be required?

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I
1	Day	SO Number	Product Code	Invoiced Weight		Day	SO Number	Product Code	Invoiced Weight
2	1/29/2019	SO-291420	26102	19040		1/30/2019	SO-293830	20502	117
3	1/30/2019	SO-292253	10400	168		1/30/2019	SO-293830	10519	45
4	1/30/2019	SO-292253	10401	168		1/30/2019	SO-293917	20700	115
5	1/30/2019	SO-293830	20502	117
6	1/30/2019	SO-293830	21617	698
7	1/30/2019	SO-293830	21618	151
8	1/30/2019	SO-293830	82100	1012
9	1/30/2019	SO-293830	10331	190
10	1/30/2019	SO-293830	10519	45
11	1/30/2019	SO-293917	20039	120
12	1/30/2019	SO-293917	20700	115
Sheet1

In I2 control+shift+enter, not just enter, and copy down:

=INDEX($D$2:$D$12,MATCH($H2,IF($A$2:$A$12=$F2,IF($B$2:$B$12=$G2,$C$2:$C$12)),0))

 

[Johnboy28]

Thanks for your help. Formula works great.

 

[Yongle]

or simply sum all the values that match all 3 criteria

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Day​	SO Number​	Product Code​	Invoiced Weight​	.​	.​	Day​	SO Number​	Product Code​	Invoiced Weight​	formula in J2 copied down​
2​	29/01/2019​	SO-291420​	26102​	19040​	​	​	30/01/2019​	SO-293830​	20502​	117​	=SUMIFS(D:D,A:A,G2,B:B,H2,C:C,I2)
3​	30/01/2019​	SO-292253​	10400​	168​	​	​	30/01/2019​	SO-293830​	10519​	45​
4​	30/01/2019​	SO-292253​	10401​	168​	​	​	30/01/2019​	SO-293917​	20700​	115​
5​	30/01/2019​	SO-293830​	20502​	117​	​	​	​	​	​	​
6​	30/01/2019​	SO-293830​	21617​	698​	​	​	​	​	​	​
7​	30/01/2019​	SO-293830​	21618​	151​	​	​	​	​	​	​
8​	30/01/2019​	SO-293830​	82100​	1012​	​	​	​	​	​	​
9​	30/01/2019​	SO-293830​	10331​	190​	​	​	​	​	​	​
10​	30/01/2019​	SO-293830​	10519​	45​	​	​	​	​	​	​
11​	30/01/2019​	SO-293917​	20039​	120​	​	​	​	​	​	​
12​	30/01/2019​	SO-293917​	20700​	115​	​	​

Sheet: Sheet3