Need Help with an INDEX(Match) formula

Susan Vieira

Hello,

I need some help sorting out an INDEX(match) formula that is not working despite my best efforts. It may be that I have been staring at it too long. I keep getting a No Match when I add "0" in search type, and I get the first value in my column when I add a "1" in search type. It seems ot be returning the first value it findss and not the value that matches what I am looking for. If I am complete honest, I don't completely understand what the 'search type" part of the formula does and these INDEX formulas always stump me. This is my Fx:

=INDEX({Task Hours -Hours}, MATCH([Task Code]@row, {Task Hours -Tasks}, 1))

This returns first value in my Hours column, but not the value that corresponds to the Task Code in that row.

=INDEX({Task Hours -Hours}, MATCH([Task Code]@row, {Task Hours -Tasks}, 0))

This returns No Match

I am trying to get the Hours for the task I have entered in my Task Code column in that row. Can anyone spot what I am doing wrong?

I was trying to mimic this example in the Help page, the difference being I am referencing columns in another sheet.

=INDEX([Price Per Unit]:[Price Per Unit], MATCH("Jacket", [Clothing Item]:[Clothing Item], 0))

Returns the value in the “Price Per Unit” column for the row that contains the value “Jacket” in the “Clothing Item” column.

$200.00

Thanks so much in advance!

Susan

Kelly Moore

Hey @Susan Vieira

The syntax of your formulas are correct-typically 0 is the one needed. Please verify that the {ranges} are correct by deleting the ranges from your formula and reinserting them - making sure the column is the correct column and that the entire column is selected (sometimes errors occur if the sheet loads slowly). Please also verify that the current row in your destination sheet where you are entering the formula does actually have a match. The [Task Code]@row needs to be an exact match to data in the {Task Hours -Tasks}

Is it possible to obtain a screenshot of both your source and destination sheets?

I expect other community members to weigh in to help troubleshoot. - oops sorry Mark - we worked this at same time

Mark Cronk

Hi Susan,

Using Match type 0 is correct unless you have the range sorted - and you keep it sorted.

=INDEX({Task Hours -Hours}, MATCH([Task Code]@row, {Task Hours -Tasks}, 0)) should work. No match with 0 and your 1st entry with 1 tells me that you have a value issue. What type is your [task code] column and your {Task Hours -Tasks} range - number, text, calculation? The {Task Hours -Tasks} should be a single column with the same column property as your [task code] column.

Mark

Kelly Moore

Hey @Susan Vieira

The syntax of your formulas are correct-typically 0 is the one needed. Please verify that the {ranges} are correct by deleting the ranges from your formula and reinserting them - making sure the column is the correct column and that the entire column is selected (sometimes errors occur if the sheet loads slowly). Please also verify that the current row in your destination sheet where you are entering the formula does actually have a match. The [Task Code]@row needs to be an exact match to data in the {Task Hours -Tasks}

Is it possible to obtain a screenshot of both your source and destination sheets?

I expect other community members to weigh in to help troubleshoot. - oops sorry Mark - we worked this at same time

Susan Vieira

Thank you @KDM & @Mark Cronk

So I checked my column properties and they are both Text columns. I reinserted my ranges and got same No MATCH. One thing to note, my Task Hours - Tasks column is a formula column and when I remove the formula and write the Task in as text, it works. If I leave it as a formula, I get the No Match. I am using a Formula to combine 2 other separate columns. I will include a screen shot. Is it because the value in the cell is not text I typed in and the result of a formula that it is not matching?

In the 3rd row, when I remove the column fx and enter "Play - Dog" as text, I get my hours properly on my main spreadsheet (0.18, partially hidden, sorry!) But I do not get the others, which is using the fx in the Tasks column.

One thing to note is that in my main spreadsheet, my Task Code column is also a Fx column with the exact same formula structure you see below. I am confused as to why it works with a Fx in Task code but not in Task.

Kelly Moore

Hey @Susan Vieira

Just for trouble shooting purposes - would you mind adding a helper column (or type in an unused text/number column) this formula to check the length of your text strings between your source sheet and your destination sheet. This will help see if you have an extra space somewhere.

=LEN(Tasks@row) or =LEN([Task Code]@row) The Length (LEN) function will return the number of characters it finds. Let's see if the same words have the same number of characters in both sheets

Susan Vieira

@KDM Good idea, I did this and the number of characters is identical.

Susan Vieira

@KDM , @Mark Cronk I found my problem. There were some space discrepancies and some column type issues with the original source columns that was populating my Task column. My species column was a multi-select drop down and when I changed it to single, most of my formulas corrected themselves. And then I found some tasks with extra spaces that snuck in there which fixed the rest of them. It all works now. Thank you so much for your help!

Mark Cronk

Interesting. Try removing all spaces from your text formula so you end up with Walk-Dog, Bathe-Dog, etc. Do you have a leading space in your species drop down entries?

Thanks for bringing a good problem to the community.

Mark

Susan Vieira

@Mark Cronk , I will be adjusting all the spaces for sure now that I know this is my problem. I had fixed all this when I built my sheet and they got inadvertently edited by someone and I did not realize this. Now I know what I have to fix. I was getting no matches because they truly weren't matching! :) Thanks all for your input.

Mark Cronk

Happy to help.

Mark