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]

​

 

[steve the fish]

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)

 

[Yongle]

One way
- add a helper column containing concatenation of the 3 values
- use Index & Match

like this

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]

Helper Column​

[/td][td]

formula
in E2 copied down​

[/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]

43494|SO-291420|26102​

[/td][td]

=A2&"|"&B2&"|"&C2​

[/td][td]

30/01/2019​

[/td][td]

SO-293830​

[/td][td]

20502​

[/td][td]

117​

[/td][td] =INDEX(D:D,MATCH(G2&"|"&H2&"|"&I2,E:E,0))[/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]

43495|SO-292253|10400​

[/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]

43495|SO-292253|10401​

[/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]

43495|SO-293830|20502​

[/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]

43495|SO-293830|21617​

[/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]

43495|SO-293830|21618​

[/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]

43495|SO-293830|82100​

[/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]

43495|SO-293830|10331​

[/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]

43495|SO-293830|10519​

[/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]

43495|SO-293917|20039​

[/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]

43495|SO-293917|20700​

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

 

[Yongle]

or use and ARRAY formula in J2 copied down
=INDEX(D:D,MATCH(1,(G2=A:A)*(H2=B:B)*(I2=C:C),0))

an array formula is committed with {CTRL}{SHIFT}{ENTER}
see worksheet below- the formula is automatically enclosed in curly brackets like this {=INDEX(D:D,MATCH(1,(G2=A:A)*(H2=B:B)*(I2=C:C),0))}
inserting them manually does not work!

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]

ARRAY 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] {=INDEX(D:D,MATCH(1,(G2=A:A)*(H2=B:B)*(I2=C:C),0))}[/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: Sheet2[/td][/tr][/table]

 

[tyija1995]

Another option using SUMPRODUCT:

=SUMPRODUCT(($A$2:$A$12=F2)*($B$2:$B$12=G2)*($C$2:$C$12=H2)*($D$2:$D$12))

 

[steve the fish]

Another option using SUMPRODUCT:

=SUMPRODUCT(($A$2:$A$12=F2)*($B$2:$B$12=G2)*($C$2:$C$12=H2)*($D$2:$D$12))

You would have to be absolutely sure each one was unique or its going to sum them up and get wrong answers. However you could then use SUMIFS also.

 

[tyija1995]

You would have to be absolutely sure each one was unique or its going to sum them up and get wrong answers. However you could then use SUMIFS also.

Good spot! I'd imagine if they weren't all unique then the invoiced weight could be a variety of numbers though? It would be impossible (based on the given criteria) to generate a single solution as it could be either A or B etc!

 

[steve the fish]

By not being unique i mean if, for example, the first line was repeated further down but with a different weight then you would sum those two weights with sumproduct or sumifs.

 

[tyija1995]

By not being unique i mean if, for example, the first line was repeated further down but with a different weight then you would sum those two weights with sumproduct or sumifs.

Right, I see! Like this then:

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Day[/TD]
[TD]SO Number[/TD]
[TD]Product Code[/TD]
[TD]Invoiced Weight[/TD]
[TD][/TD]
[TD]Day[/TD]
[TD]SO Number[/TD]
[TD]Product Code[/TD]
[TD]Invoiced Weight[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]29/01/2019[/TD]
[TD]SO-291420[/TD]
[TD]26102[/TD]
[TD]19040[/TD]
[TD][/TD]
[TD]29/01/2019[/TD]
[TD]SO-291420[/TD]
[TD]26102[/TD]
[TD]19100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]30/01/2019[/TD]
[TD]SO-292253[/TD]
[TD]10400[/TD]
[TD]168[/TD]
[TD][/TD]
[TD]30/01/2019[/TD]
[TD]SO-292253[/TD]
[TD]10400[/TD]
[TD]168[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]29/01/2019[/TD]
[TD]SO-291420[/TD]
[TD]26102[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

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).