VLOOKUP w Dates, get "No match" back

Hi there, I am trying to use a VLOOUP formula that references a column in my existing sheet (a date) and then goes into a fiscal week lookup table. Matches the original date and displays the fiscal week match. To simplify I have spelled out every date in column 1 and fiscal week reference in column 2.

My formula is this and i keep getting a no match message even when I limit the search to only the row where I know there is a match.

=VLOOKUP([Start Date (Planning)]@row, {FY_lookup Range 5}, 2, true)

Start Date (Planning) - column in my original sheet

FY_lookup = Lookup sheet

Range5 = Column 1 and 2 where 1 has all dates and 2 has all fiscal weeks.

Answers

  • Kelly Drake
    Kelly Drake Overachievers Alumni

    INDEX/MATCH might work better for you!

    Try this:

    =INDEX({FY Sheet FW Column}, MATCH([Start Date (Planning)]@row, {FY Sheet Date column}, 0))

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • Thanks for quick reply, Ok I tried this

    =INDEX({FY_lookup Range 3}, [Start Date (Planning)]@row, {FY_lookup Range 4}, 0)

    And the message back is: Incorrect Argument Set


    Range 3 is dates

    Range 4 is Fiscal week list

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Frida Taylor

    It looks like you're just missing the MATCH function in your formula from Kelly's example.

    Try adding it in, with the extra closing parentheses at the end, bolded here:


    =INDEX({FY_lookup Range 3}, MATCH([Start Date (Planning)]@row, {FY_lookup Range 4}, 0))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!