Index/Match Question

Camille Adriao

Hello. Looking for assistance with a formula. I am working off of 2 sheets. Sheet 1 is my Master Schedule and Sheet 2 is imported data containing (1) column of data only. The Primary column on both sheets is the Job Number field. What I'm hoping to do is run through all Job Numbers on Sheet 1 and if the Job Number is located on Sheet 2 I would like to checkmark the Exhibitor List field on Sheet 1. I cannot use vlookup as I'm already at the max limitation for that. Figured Index/Match would be the best bet, but not sure how to set the formula up. I would run newly imported data on Sheet 2 daily.

Sheet 1

Sheet 2

Paul Newcome

To maintain previously checked fields, you are goin to need to maintain the historical data in Sheet 2 instead of overwriting it.

Paul Newcome

Try something like this...

=CONTAINS([Job Number]@row, {Other Sheet Job Number Column})

Camille Adriao

@Paul Newcome what do I add this to act as the checkmark identifier? If I use the above only all cells are blank. Thank you!

Paul Newcome

The CONTAINS function output a true/false value which should check the box if it is found. Do any of your Job Numbers have leading zeros?

Camille Adriao

@Paul Newcome No, none of the job numbers have leading zeros. I believe that the problem is with my data list. Is there a way to update the statement to exclude looking at job numbers on sheet 1 that have previously been check marked. My data list on sheet 2 is only 30 days out, but I have job numbers that were previously checked and I want to keep those job numbers checked and only look at events unchecked moving forward. Thank you!

Paul Newcome

Let me make sure I understand correctly...

We want a formula on Sheet 1 that will check the box if the Job Number is found on Sheet 2?

Camille Adriao

@Paul Newcome Yes, correct. Without affecting any fields that were previously check marked.

Paul Newcome

To maintain previously checked fields, you are goin to need to maintain the historical data in Sheet 2 instead of overwriting it.