IF INDEX MATCH w/ DATE & CHECKBOX

Sam Lugiano

Hi,

I am trying to capture the date a treatment was done on a separate sheet. As you can see by the screenshot, I just want to index and capture the DATE OF VISIT when the checkbox is checked under RE-EXAM and match to the BARCODE. I have tried several IF(INDEX(MATCH formulas but none of them have worked. Your help is much appreciated. Thanks!

Kelly Moore

Hey @SLUGGO

If I understand correctly, you have another sheet with barcodes on it and you want to use that sheet to Index the date when the barcode on this source sheet matches your other sheet (target sheet) and this source sheet checkbox is checked?

Since you have more than one criteria, try an Index/Collect instead of an Index/Match.

=INDEX(COLLECT({Source sheet above Date of Visit column}, {Source sheet column above Barcode}, [Target sheet Barcode]@row, {source sheet above Re-Exam column}, 1),1)

The first 1 in the formula is referencing the checkmarks in the Re-exam column. The second 1 is giving a row index for your INDEX function. Since you are collecting a Date field, make sure the column that contains this formula is formatted also as a date field.

Since this is a cross reference formula, you cannot just copy paste and edit column names. You must insert the cross references manually from the formula blue link, into your formula.

Does that work for you?

Kelly

https://help.smartsheet.com/function/collect

Kelly Moore

Hey @SLUGGO

If I understand correctly, you have another sheet with barcodes on it and you want to use that sheet to Index the date when the barcode on this source sheet matches your other sheet (target sheet) and this source sheet checkbox is checked?

Since you have more than one criteria, try an Index/Collect instead of an Index/Match.

=INDEX(COLLECT({Source sheet above Date of Visit column}, {Source sheet column above Barcode}, [Target sheet Barcode]@row, {source sheet above Re-Exam column}, 1),1)

The first 1 in the formula is referencing the checkmarks in the Re-exam column. The second 1 is giving a row index for your INDEX function. Since you are collecting a Date field, make sure the column that contains this formula is formatted also as a date field.

Since this is a cross reference formula, you cannot just copy paste and edit column names. You must insert the cross references manually from the formula blue link, into your formula.

Does that work for you?

Kelly

https://help.smartsheet.com/function/collect

Sam Lugiano

Thank you Kelly! Worked perfectly.

Kelly Moore

Happy it worked for you. Have a great Friday