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))

ss - index match find left.png

Find more posts tagged with

Formulas

Comments

L_123

=index(collect({O_Type},{O_Type_ID},left(@cell,2) = LEFT([Type For List]@row, 2)),1)

* Untested

L_123

=index(collect({O_Type},{O_Type_ID},find(LEFT([Type For List]@row,2),@cell)>0,1)

Would be another option if you have different formats and just want to find the number

L_123

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

info.isaackwikcut92946

thank you L@123.

I tried the formula but can not get it to work.... #UNPARSEABLE

I have not used collect or @cell yet.

L_123

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

L_123

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)

info.isaackwikcut92946

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.

L_123

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.

info.isaackwikcut92946

Thank you so much. Yes that formula works very nicely

Very much appreciated.

L_123

Finally haha Took me long enough.

Not a problem

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])