IF INDEX MATCH w/ DATE & CHECKBOX
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 REEXAM 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

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 ReExam column}, 1),1)
The first 1 in the formula is referencing the checkmarks in the Reexam 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

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 ReExam column}, 1),1)
The first 1 in the formula is referencing the checkmarks in the Reexam 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

Thank you Kelly! Worked perfectly.

Happy it worked for you. Have a great Friday
Help Article Resources
Categories
Check out the Formula Handbook template!