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 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
-
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
-
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
-
Thank you Kelly! Worked perfectly.
-
Happy it worked for you. Have a great Friday
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!