Simple Index Match formula link to another sheet returning NO MATCH

JDeVoll
JDeVoll ✭✭
edited 05/18/23 in Formulas and Functions

Simple Index Match formula link to another sheet returning NO MATCH. I'm new to smart sheet and I'm fairly good with formulas in excel. Could my issue be mixing text and date data types? Any help would be greatly appreciated. I have not been able to find a reason in searching other posts.

Here is an example formula (it is referencing the cell above it to match a project schedule sheet task and return an end date):

=INDEX({1. West Project Tracker Working File Range 1 Task}, MATCH([Feasy Model Start Date]1, {1. West Project Tracker Working File Range 2 EndDt}, 0))



Answers

  • JDeVoll
    JDeVoll ✭✭

    Update

    It is a column type issue between text/number and date. I added a VLOOKUP formula and it was returning INVALID COLUMN VALUE. I changed the column types to Date and now the VLOOKUP is working but the INDEX MATCH still will not. Does any one know why?



  • Hi @JDeVoll

    A No Match error usually means that the MATCH portion is unable to find a cell on your source sheet that exactly matches the text you have in that top cell.

    Based on your formula, it looks like you may have your ranges swapped around. The MATCH portion is where you want to list the column that has a matching value (the one with the words "Feasy Model Start Date").

    The first range listed in an INDEX function is the column that has the data you want to bring back, so in your case, the Start Date (or end date?) column with dates.

    =INDEX({Column to Return}, MATCH([Value to Match]1, {Column with matching value},0))


    In your case:

    =INDEX({Date Column}, MATCH([Top Row]1, {Column with matching Text},0))


    Try:

    =INDEX({1. West Project Tracker Working File Range 2 EndDt}, MATCH([Feasy Model Start Date]1, {1. West Project Tracker Working File Range 1 Task}, 0))


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!