Date Range, Return Text, Formula VLookup #Spill! Error

Kokosek - when I do that I get an #N/A error - it HAS to return a text answer, like FY20 or FY22, etc....

In my original workbook I cannot get the formula to work - either how I had it listed originally or the change.

ok...so now I'm confused. Which one is the proper formula (format?) ?

The 'Srvc Provided' is in the same table as the formula, in column C. It has the date of service listed that needs to be placed into a fiscal year and that fiscal year (FY19, FY20, FY21, or FY22) needs to be listed in column K (highlighted in attached file in yellow) so that it can be used in a variety of pivot tables in other tabs.

The FYTABLE is found on a different Tab (see the second tab in the attached simplified file - where the formula works fine). The only things listed in the table are the dates, in the first column of the table, and the text to be entered, in the second column...there are only 2 columns...where the fiscal years are listed.

These conditions are identical in the original workbook, although, there are several more columns listed, and there are three other tables found on the tab "Tables (Update as Necessary).....when I open the Existing Connections, the FYTABLE is listed.

The only other difference between the sample file and my original file is that I placed the TYTABLE below another named table, will that affect it? Do I need to place it in columns on its own?

You need to use

Formula:

Please Login or Register  to view this content.

The other way will not work in a structured table, as you cannot put s spill formula in a table

Thank you, Fluff13...I am using that formula and I get #N/A as the result, when the date in Srvc Provided is 12/22/21 and should be returning FY22.

I did move the table to its own columns (even though I'm not sure it really mattered, I wanted to make sure it wasn't causing an issue).

BTW...I did find the overwrite all cells in column feature...and did use that, so that part of my question has been solved

You should only get #N/A if one of the dates is before any date in the FYTable, or if you enter a date as text.

Fluff13...I verified that the column cell formatting is set to date, in both the Srvc Provided column and the FYTABLE date column....I am still getting the #N/A...and the starting date of the FYTABLE is 10/1/2015 and the ending date is 10/1/23....12/22/21 is between those dates. Also, when I copied the formula to all the cells in the column, they were all #N/A....so I am still really confused as to why this formula worked in the sample file, but is not in my original file....I even copied the FYTABLE from the sample file into the original file so that it was identical. I copied the formula from the sample to the original....I 're-attached' in the original to the Srvc Provided and FYTABLE in the formulas by retyping them and selecting from the drop down. Still same result. When I changed the column to a different column with the pay date listed in it...the results were correct....so, it's probably something with that original column, the Srvc Provided?

Select the Srvc Provided column & change the format to General. What do you see?

Changing the format to General did not work....still receiving #N/A.

=VLOOKUP([@[Srvc Provided]],FYTABLE,2,TRUE) - this format provides #N/A

When I change it to the PayDate column, the formula changes to =VLOOKUP([@PayDate],FYTABLE,2,TRUE) - This returns the correct response. I do the change by clicking on the lookupvalue and typing in the column name....selecting it... and when I'm done, the formula automatically changes....could it be the space in the column heading?

No....removing the space in the column title still returns #N/A =VLOOKUP([@SRVCProvided],FYTABLE,2,TRUE)

Changing the format to General did not work....still receiving #N/A.

Yes but what do you see in Srvc Provided column, dates or numbers?

I'm seeing dates....and the formatting for PayDate is identical, so using PayDate is working, but not Srvc Provided.

If you still see dates after changing the format to General, then they are text & not real dates. If they were real dates you would see a 5 digit number.

ok...out of thousands of rows of data, there are only 4 cells that changed to 5 digits.....and I cannot get the rest of them to change....do you have a secret?

You could try text to columns, select delimited, next, clear all checkboxes, next, select date,finish

Genius! Thank you for that secret! It is now working! I have added it to the steps that the user needs to complete when they paste in new data.

I really appreciate your assistance with working through this craziness!

Glad to help & thanks for the feedback.