If formula to return a date

Darla Brown

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

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 !

Vojtech Gajdos

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

Darla Brown

Almost. The formula returns #NO MATCH even for those with a date in the cell.

Darla Brown

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.

Paul Newcome

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.

Darla Brown

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

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 !