If formula to return a date
Hi!
I need a formula to look at another sheet, if it matches criteria, return the date.
I have one sheet that lists all of the requests for W9. On my base sheet, I need to look at the W9 sheet and show the date the W9 was received. I have tried an IF formula, but continue to get Invalid Data Type. This is what I used: =IF({W9 Tracker Property}, "7341", {W9 Tracker Date Rec}).
I'm sure it's something simple, but I've tried reversing the order, using =, eliminating "", and etc.
Thanks in advance for any help!
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
Best Answer
-
UPDATE (FIXED):
I did go back and check the source data. In between the time I had initiated this discussion and began the work, someone had entered a duplicate entry. This did cause the blank cell. Once I deleted the duplicate, the date pulled in.
To prepare for any future duplications with multiple properties using same agencies, I made a combo formula column: =[Property #]@row + [Agency Name (Must match W9)]@row. Then I used that column in my INDEX formula: =INDEX({W9 Tracker Date Rec}, MATCH("7308" + [Name of Agency]@row, {W9 Tracker combo}, 0)).
This is working. Thanks for the help @Paul Newcome and @Vojtech Gajdos !
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
Answers
-
Hi @Darla Brown
Try this:
=INDEX({W9 Tracker Date Rec}, MATCH("7341", {W9 Tracker Property}, 0))
It should do the job you are after...
Regards
Vojtech
-
Almost. The formula returns #NO MATCH even for those with a date in the cell.
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
UPDATE:
I realized my unique identifier should not have been the property but the agency@row instead. I made that change:
=INDEX({W9 Tracker Date Rec}, MATCH([Name of Agency]@row, {W9 Tracker Agency}, 0))
But now it is returning a blank cell instead of the date.
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
Double check your source data. Find the very top/first row that matches for [Name of Agency] and that is the row your INDEX function is going to be pulling from.
-
On this particular sheet there is only one agency with this name, but I need to think about that for the future if there are more than one. Still not pulling in a date though.
Thanks Paul.
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
UPDATE (FIXED):
I did go back and check the source data. In between the time I had initiated this discussion and began the work, someone had entered a duplicate entry. This did cause the blank cell. Once I deleted the duplicate, the date pulled in.
To prepare for any future duplications with multiple properties using same agencies, I made a combo formula column: =[Property #]@row + [Agency Name (Must match W9)]@row. Then I used that column in my INDEX formula: =INDEX({W9 Tracker Date Rec}, MATCH("7308" + [Name of Agency]@row, {W9 Tracker combo}, 0)).
This is working. Thanks for the help @Paul Newcome and @Vojtech Gajdos !
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!