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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!