Formula issue

Susan van Niekerk

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?

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.

Susan van Niekerk

I have deleted, saved and retype in fresh

Susan van Niekerk

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

@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.

Susan van Niekerk

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

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.