IF INDEX MATCH w/ DATE & CHECKBOX

Options

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!


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 06/25/21 Answer ✓
    Options

    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


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 06/25/21 Answer ✓
    Options

    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


  • Sam Lugiano
    Sam Lugiano ✭✭✭✭✭
    Options

    Thank you Kelly! Worked perfectly.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Happy it worked for you. Have a great Friday

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!