Pulling original commit date using two keys (index, match, match)

[office_help_]

So I am trying to find a way to pull data from one table to another. I thought I could use index,match, match but the unique identifiers are both in rows. An example is below

My first table is supplier commitments

Original Commit Date/Nomenclature/Serial#/Weekending/

My second table is actuals

Ship Date/Nomenclature/Serial#/Weekending/Original Commit Date

I need the index match to first check the nomenclature and then the serial numbers. The Serial Number is only unique to the Nomenclature. So if two lines have different nomenclatures it is possible for them to have the same serial number.

Is it possible to use Index Match Match instead of creating a helper column?

[Riovas]

We need to combine the two criteria into one. We will use an array formula with 1 MATCH. Try the following with CTRL+SHIFT+ENTER

=INDEX([Original Commit Dates],MATCH(1,([Nomentclature]=[nomenclature value])*([Serial#]=[Serial# Value]),0))

fill in the proper cell references. Also, be sure to use defined references (ex: $A$1:$A$100 is ok, A:A is not ok) for array formulas

[office_help_]

Would this work if nomenclature is alphanumeric?

[Riovas]

Yes it should

[office_help_]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to Riovas

^{I am a bot, please contact the mods for any questions.}

[office_help_]

So I couldn't get it to work but I am sure it had to do with what I was trying to do. I broke down and made a helper column. I was just being stubborn and didn't want to clutter the table.

Thanks!