Conditional Index Match

EEGordon

Hey y'all,

I have a formula that does an index match to a separate sheet however, there are no unique ids that I could match. I have narrowed down the data to a point where if I could add just one condition to the formula I could get the information I need. The source sheet has thousands of rows and the target sheet has about 600 so I'd rather not have to setup unique IDs. The employee unique ids typically work but since the source sheet may have multiple entries for an employee the formula stops at the first occurrence. The formula works if I sort the source sheet the right way but I can't always ensure that it stays sorted that way. The current formula is =INDEX({Uniform Order Tracking- CS Jacket Style}, MATCH(EID@row, {Uniform Order Tracking- CS EID}, 0)) but I only want it to pull in the info to the target sheet if the "Order Type" column (source sheet) is "Jacket Order"

Paul Newcome

You will need an INDEX/COLLECT instead.

=INDEX(COLLECT({Uniform Order Tracking- CS Jacket Style}, {Uniform Order Tracking- CS EID}, EID@row, {Source Sheet Order Type}, "Jacket Order"), 1)

Paul Newcome

You will need an INDEX/COLLECT instead.

=INDEX(COLLECT({Uniform Order Tracking- CS Jacket Style}, {Uniform Order Tracking- CS EID}, EID@row, {Source Sheet Order Type}, "Jacket Order"), 1)

EEGordon

Thanks Paul!

Paul Newcome

Happy to help. 👍️