Formula issue

Hi All,


I have an index match formula - that is working fine except on one row

I have checked that the numbers are correct in the source sheets - but it will just not bring back the name - all others rows are working

What could be the issue?



Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi @Susan van Niekerk

    You probably would have already checked there's no space " " before or after WS.721 (for example), either in the Project Services Hot Desks sheet or Project Service Roster 2023 sheet?

    I know this was an issue for me when original data was pasted from excel without being trimmed.

    Hope this helps and that you have a great day,

    Jason

  • I have deleted, saved and retype in fresh

  • Susan van Niekerk
    Susan van Niekerk ✭✭✭✭
    edited 10/11/23

    If I look at the sheet it is pulling from - it all looks correct - but will not pull the name pulls the word Vacant

    Have checked to see if there are duplicates in either source sheet



  • Razetto
    Razetto ✭✭✭✭✭✭

    @Susan van Niekerk Hi, I wonder if the problem is that your formula is trying to read the cell at the source sheet that's actually made up of a formula, or the cells are linked to another source; hard to tell from here.

  • So "source sheet" is link to the individual Roster which each team updates weekly

    If the was an error with that then

    all index matches would be incorrect not just one?

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi @Susan van Niekerk

    You mentioned that you've checked to see if there are any duplicates in either source sheet, which is a good move. However, in the same response there was a screenshot of the desk names where I notice there are two instances of WS.733 and WS.736? Was this screenshot taken before your double check, or is this by design?

    I ask because I notice the second instance is just before WS.721 which is returning a #NO MATCH result. One may not have anything to do with the other, but I thought it best to seek clarification, since I understand the match function with a search-type of 0 finds the first exact match. I notice the following Desk number (WS.7123) has no problem returning "Vacant", so it still seems it's something else.

    As a troubleshooting exercise, I would access the source sheets and use a helper column with just the MATCH Function to see if it returns a positive result. This can then help rule out the Match function within the formula.

    Hope this helps and that you have a great day,

    Jason

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!