3 value lookup

[Johnboy28]

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

Thank you

[TABLE="width: 1042"]
<tbody>[TR]
[TD="align: center"]Day[/TD]
[TD="align: center"]SO Number[/TD]
[TD="align: center"]Product Code[/TD]
[TD="align: center"]Invoiced Weight[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]SO Number[/TD]
[TD="align: center"]Product Code[/TD]
[TD="align: center"]Invoiced Weight[/TD]
[/TR]
[TR]
[TD="align: center"]29/01/2019[/TD]
[TD="align: center"]SO-291420[/TD]
[TD="align: center"]26102[/TD]
[TD="align: center"]19040[/TD]
[TD="align: center"][/TD]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293830[/TD]
[TD="align: center"]20502[/TD]
[TD="align: center"] ??[/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-292253[/TD]
[TD="align: center"]10400[/TD]
[TD="align: center"]168[/TD]
[TD="align: center"][/TD]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293830[/TD]
[TD="align: center"]10519[/TD]
[TD="align: center"] ??[/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-292253[/TD]
[TD="align: center"]10401[/TD]
[TD="align: center"]168[/TD]
[TD="align: center"][/TD]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293917[/TD]
[TD="align: center"]20700[/TD]
[TD="align: center"] ??[/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293830[/TD]
[TD="align: center"]20502[/TD]
[TD="align: center"]117[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293830[/TD]
[TD="align: center"]21617[/TD]
[TD="align: center"]698[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293830[/TD]
[TD="align: center"]21618[/TD]
[TD="align: center"]151[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293830[/TD]
[TD="align: center"]82100[/TD]
[TD="align: center"]1012[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293830[/TD]
[TD="align: center"]10331[/TD]
[TD="align: center"]190[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293830[/TD]
[TD="align: center"]10519[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293917[/TD]
[TD="align: center"]20039[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30/01/2019[/TD]
[TD="align: center"]SO-293917[/TD]
[TD="align: center"]20700[/TD]
[TD="align: center"]115[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

Day​

[/td][td]

SO Number​

[/td][td]

Product
Code​

[/td][td]

Invoiced
Weight​

[/td][td]

.​

[/td][td]

.​

[/td][td]

Day​

[/td][td]

SO Number​

[/td][td]

Product
Code​

[/td][td]

Invoiced
Weight​

[/td][td]

formula
in J2 copied down​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

29/01/2019​

[/td][td]

SO-291420​

[/td][td]

26102​

[/td][td]

19040​

[/td][td]

​

[/td][td]

​

[/td][td]

30/01/2019​

[/td][td]

SO-293830​

[/td][td]

20502​

[/td][td]

117​

[/td][td] =SUMIFS(D:D,A:A,G2,B:B,H2,C:C,I2)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

30/01/2019​

[/td][td]

SO-292253​

[/td][td]

10400​

[/td][td]

168​

[/td][td]

​

[/td][td]

​

[/td][td]

30/01/2019​

[/td][td]

SO-293830​

[/td][td]

10519​

[/td][td]

45​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

30/01/2019​

[/td][td]

SO-292253​

[/td][td]

10401​

[/td][td]

168​

[/td][td]

​

[/td][td]

​

[/td][td]

30/01/2019​

[/td][td]

SO-293917​

[/td][td]

20700​

[/td][td]

115​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

30/01/2019​

[/td][td]

SO-293830​

[/td][td]

20502​

[/td][td]

117​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

30/01/2019​

[/td][td]

SO-293830​

[/td][td]

21617​

[/td][td]

698​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

30/01/2019​

[/td][td]

SO-293830​

[/td][td]

21618​

[/td][td]

151​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

30/01/2019​

[/td][td]

SO-293830​

[/td][td]

82100​

[/td][td]

1012​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

30/01/2019​

[/td][td]

SO-293830​

[/td][td]

10331​

[/td][td]

190​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

30/01/2019​

[/td][td]

SO-293830​

[/td][td]

10519​

[/td][td]

45​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

30/01/2019​

[/td][td]

SO-293917​

[/td][td]

20039​

[/td][td]

120​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

30/01/2019​

[/td][td]

SO-293917​

[/td][td]

20700​

[/td][td]

115​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]