How to get a value after a character, from a string based on a text match using formula?

Question

I got the following value:

tradicional;cropped$9$10;mullet$5$7 

In cell A1, I can choose between tradicional, cropped and mullet. In cell A2, I pick 1, or 2.

If I pick cropped and 2, the value to be returned would be 10.

If I pick mullet and 1, the value to be returned would be 5. If

I'd go for len and left, but I don't see how this is going to work using the matching criteria.

Here's a practical example: https://docs.google.com/spreadsheets/d/1dFzXmtKj15EzApTKUKv8yF7_mAIB1COPSgMLMMmFE4E/edit?usp=sharing

Appreciate your help.

Answer 1

Per my comments to your original post, I feel that there is a lot we don't know about your bigger goal. But as you aren't able to provide that, this solution will work for your one exact example.

Place the following formula in C4:

=ArrayFormula(IFERROR(VLOOKUP(A4;SPLIT(FLATTEN(SPLIT(E4;";"));"$");B4+1;FALSE)))

(See the new sheet "Erik Help.")

The inner SPLIT splits the E4 string at every semicolon.

FLATTEN sends that all to one column.

The outer SPLIT then splits at each "$".

VLOOKUP can then try to find the Col-A term in the first column of the resulting virtual chart. If found, it will return the column value that matches the Col-B value + 1 (since column 1 of the virtual array is the labels, e.g., 'tradicional,' etc.).

If no match is found for both the Col-A and Col-B data, then IFERROR returns null.