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.
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
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.
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.
Works great, will save me a great deal of time. Thanks so Much.
Bookmarks