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>

​

 

[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

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

Sheet: Sheet1

 

[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

	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​	ARRAY formula in J2 copied down​
2​	29/01/2019​	SO-291420​	26102​	19040​	​	​	30/01/2019​	SO-293830​	20502​	117​	{=INDEX(D:D,MATCH(1,(G2=A:A)*(H2=B:B)*(I2=C:C),0))}
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: Sheet2

 

[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:

	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	29/01/2019	SO-291420	26102	19040		29/01/2019	SO-291420	26102	19100
3	30/01/2019	SO-292253	10400	168		30/01/2019	SO-292253	10400	168
4	29/01/2019	SO-291420	26102	60

<tbody>
</tbody>

 

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