Need to match 2 criteria with 2 criteria to pick up a value

**ctiger** · 2013-12-12, 09:13 PM

Hi All, I have a list of data that has (Col A) item, (Col B) lot# and (Col C) quantity. In Columns DEF I have the same but column E (Lot#) is blank. Is there a formula I can use that would match Item and Quantity (A and C) with (D and F) and then drop the Lot # form column B into column E if there is a match?

Item	lot	Amt	Items	Lot #s	Yds
10429.50.0	N49950	2.5	0201.019FLAME.0	T37530	9
10651.1.0		2	10076.9.0	937198-01	2.5
1100MG.MULTI STRIPE.0		57	10337.1.0	973924-01	2.75
1100MG.MULTI STRIPE.0		5.375	10337.101.0	I08572	7.75
11074.4.0		1	10337.101.0	973926-01	6.75
11820.10.0		2.25	10337.330.0	B87337	5
10337.101.0	I08572	7.75	10337.6.0	973923-01	2.375
11820.101.0		5	10370.515.0	938084	7
11820.111.0		17.625	10397.16.0	937622-01	3.25
11820.1111.0		5.625	10397.16.0	937622-02	3
11820.1212.0		9.625	10397.3.0	937621-01	6
11820.1616.0		4.5	10397.9.0	748647	8
11836.9.0		32.5	10422.4.0	961921-01	2
11898.11.0		1.625	10429.50.0	K72557	38.875
11898.4.0		2.625	10429.50.0	N49950	2.5
11898.505.0		2.5	10429.9.0	W45328	8.75

**ctiger** · 2013-12-12, 09:33 PM

My mistake on the original description :drop the Lot # form column B into column E if there is a match? Should be the other way around.

**Hercules1946** · 2013-12-14, 09:21 PM

Try this array formula:

=INDEX($E$3:$E$19,MATCH($A4&$C4,$D$3:$D$19&$F$3:$F$19,0))
Ive used your layout above, with the
addition of one row at the bottom to provide a record that matches with your second data row.
***IMPORTANT***
with this type of formula you have to code it to suit a particular cell, so that when its copied to other cells, Excel makes the correct adjyustments for the new position. This one is coded for B4, because is coded to pick up A4 and C4, so this is where you need to put it first. If you are forced to start on another row (eg B6, then amend A4, C4 to A6 , C6.
Next you need to adjust the groups of range addresses to suit the real size of your table of data.
Then click the cell with your adjusted version of the formula in. Press F2 to place the formula in Edit mode. Press CONTROL+SHIFT+ENTER
If alls well you will see (in the formula bar that Excel has placed brace characters{} at either end of the formula.
This is the only way to do this, and indicates that the formula has array status. If you amend the formula at any time, this process must be repeated.
When the braces are in place, you can copy the formula with its braces to other cells in the column where you need a search result. If you want to override no match errors you can add IFERROR or IF(ISERROR( to the formula.