Need Help with an INDEX(Match) formula
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
Best Answer

Hey @Susan Vieira
The syntax of your formulas are correcttypically 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
Answers

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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Hey @Susan Vieira
The syntax of your formulas are correcttypically 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

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.

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

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

@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 multiselect 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!

Interesting. Try removing all spaces from your text formula so you end up with WalkDog, BatheDog, etc. Do you have a leading space in your species drop down entries?
Thanks for bringing a good problem to the community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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

Happy to help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!