Index, Match with Find and Left function
I am trying to get a formula to look up a value with the INDEX / MATCH function and the Search_Value being the numbers up to a "-" dash from the "Type For List" column.
The index/match does not seem to like the LEFT or FIND functions
Has anyone come across this or has a work around?
=INDEX({O_Type}, MATCH((LEFT([Type For List]@row, 2)), {O_Type_ID}, 0))
Comments
-
-
reread the problem haha this one will search out the "-" save what is to the left of it, and search for it in your results column, and return the associated value
=index(collect({O_Type},{O_Type_ID},left([Type For List]@row,find("-",[Type For List]@row))=left(@cell,find("-",[Type For List]@row)),1)
*Untested
-
thank you L@123.
I tried the formula but can not get it to work.... #UNPARSEABLE
I have not used collect or @cell yet.
-
Not really sure what is missing. Try this
=index(collect({O_Type},{O_Type_ID},left([Type For List]@row,find("-",[Type For List]@row))=iferror(left(@cell,find("-",[Type For List]@row)),""),1)
also, check your other sheet references. They need to be the same number of cells, which could cause the issue.
https://help.smartsheet.com/function/collect
https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
-
Found it. Missing a parenthesis
=index(collect({O_Type},{O_Type_ID},left([Type For List]@row,find("-",[Type For List]@row))=iferror(left(@cell,find("-",[Type For List]@row)),"")),1)
Tested Version that works (R = Return Column, S = Search Column, T = Value to parse for the search)
=INDEX(COLLECT(R:R, S:S, LEFT(T@row, FIND("-", T@row)) = IFERROR(LEFT(@cell, FIND("-", T@row)), "")), 1)
-
Thank you very much for taking the time to help me with this formula.
I just can not get it to work for me... can you see what i am missing. I copied your tested formula and inserted my references/cell.
-
Alright thanks. That screenshot makes this a lot clearer. I misunderstood the layout of the sheets
=INDEX(COLLECT({O_Type}, {O_Type_ID}, @cell = value(LEFT([Type For List]@row, FIND("-", [Type For List]@row)-1))) , 1)
Test that out and let me know if it works for you.
-
Thank you so much. Yes that formula works very nicely
Very much appreciated.
-
Finally haha Took me long enough.
Not a problem
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!