match 2 criteria

[thuddleston11]

I have 2 tables that are coming onto my spreadsheet automacticlly everyday and they can't come to the some tab. I need to match up the date and department # on both sheets and then return the UOM to the other sheet.

I am not familiar with MATCH and I think that is what I need to use so I need help. Please see attached workbookkronos uom.xlsx

[Pete_UK]

As you have a lot of data, you want to avoid array-formula solutions, so I would advise using a helper column in sheets FRX and Sheet2. Put this formula in D2 of FRX sheet:

=A2&"_"&B2

It should copy down automatically. Do the same for Sheet2.

Then you can use this formula in C2 of the krono sheet:

=IFERROR(INDEX(FRX!C:C,MATCH(A2&"_"&B2,FRX!D:D,0)),0) + IFERROR(INDEX(Sheet2!C:C,MATCH(A2&"_"&B2,Sheet2!D:D,0)),0)

then copy this down to the bottom of your data by double-clicking the fill handle (the small black square in the bottom right corner of the cursor) - this took several seconds to calculate on my machine, so you might want to fix the values.

Hope this helps.

Pete

[thuddleston11]

thank you this works perfect. I don't understand the first formula though?

[Pete_UK]

The first formula just joins the date with the department (separated by an underscore) so that we then have to check and match only one column, as the MATCH functions in the second formula also join column A with column B with an underscore between them and uses that to find a match with column D in the two sheets.

Pete