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

C

ctiger

New member
Joined
Nov 4, 2013
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2013
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?
ItemlotAmtItemsLot #sYds
10429.50.0N499502.50201.019FLAME.0T375309
10651.1.0210076.9.0937198-012.5
1100MG.MULTI STRIPE.05710337.1.0973924-012.75
1100MG.MULTI STRIPE.05.37510337.101.0I085727.75
11074.4.0110337.101.0973926-016.75
11820.10.02.2510337.330.0B873375
10337.101.0I085727.7510337.6.0973923-012.375
11820.101.0510370.515.09380847
11820.111.017.62510397.16.0937622-013.25
11820.1111.05.62510397.16.0937622-023
11820.1212.09.62510397.3.0937621-016
11820.1616.04.510397.9.07486478
11836.9.032.510422.4.0961921-012
11898.11.01.62510429.50.0K7255738.875
11898.4.02.62510429.50.0N499502.5
11898.505.02.510429.9.0W453288.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.
 

Attachments

  • Lookup.xlsm
    27.5 KB · Views: 15
Works great, will save me a great deal of time. Thanks so Much.
 
ctiger said:
Works great, will save me a great deal of time. Thanks so Much.
Click to expand...

Glad to be of help. I just noticed that in the example I left the formula in B4 in text form, so sorry about that :)
Obviously you figured it out though.
 
You must log in or register to reply here.
Back
Top