Lookup text in shorter text

Hi all,

I have an question regarding the look of text in a table. I have periodic texts I want to look up and return a specific value.

I want to look up the following texts:
"Test/ Red 01-2019"
"Test/ Red 02-2019"
"Test/ Blue 01-2019"
"Test/ Blue 02-2019"

In a table that contains 2 columns:
Text --- Value
Test/ Red --- 5
Test/ Yellow --- 7
Test/ Blue --- 9
Test/ Purple --- 11

So "Test/ Red 01-2019" should give me the value 5. Does anyone know if this is possible? I know it is possible to look up "Test/ Red" in "Test/ Red 01-2019", but I want it the other way around.

Is this possible?

Thanks

Instead of using "Test/ Red 01-2019" as your lookup value, you could use LEFT("Test/ Red 01-2019",9) instead.

Hope this helps.

Pete

Hi Pete,

You are correct yes. But however I was using this as a test and it can be 100 different titles instead of both 'test' and 'red'.

I need something that like does a search from the first character until it finds a match, so in this case until the letter D of red. Without having to sum up the amount of characters cause this can be different every time.

Thanks!

Hi Ali,

I tried your suggestion ofcourse, but then it is showing me an error if I do: =LEFT(A3,FIND("0",A3,-2)) I get an error (A3 is my lookup cell "Test/ Red 01-2019).
If I do that it says #VALUE!. Besides that there is also the possibility that the text will say "Test/ Red 12-2019". In that case the 0 will be of no use I guess?

Thanks for your time so far.
Devon

Here is the attachment with what the result should be. Yellow is where the formula is. The right table is where it should find the data and the left is the input.

Ah yes that is it!!!!!!!!!!!!!!!!

Thank you very much Ali!

Change the formula in B2 to this:

=LEFT(A2,FIND(" ",A2,FIND("/ ",A2)+2)-1)

Then in C2 you can have this:

=VLOOKUP(B2,$F$2:$G$6,2,0)

You may need to use semicolons ( ; ) instead of commas ( , ) in the formulae, depending on your regional settings. Then copy the formulae down.

You could, of course, replace the B2 in the second formula with the first formula to have this:

=VLOOKUP(LEFT(A2,FIND(" ",A2,FIND("/ ",A2)+2)-1),$F$2:$G$6,2,0)

then you wouldn't need column B.

Hope this helps.

Pete

Hi Pete,

Thank you I see this also brings the result. On other question though. Imagine I would have:
Test/ Dummie Drop 2017
Test/ Dummie Drop 2018
Test/ Dummie Drop 2019

What would I have to adjust in the =LEFT(A2,FIND(" ",A2,FIND("/ ",A2)+2)-1) to get the word "Test/ Dummie Drop".
Cause when I adjust the +2 to +1 it only gives me "Test/" and when I adjust it to +3 it still shows "Test/ Dummie", same as with +2.

See file blue cells.

Thanks in advance.

I had no idea what was possible. I just want to know how to read this latest formula from Pete then I am done.

Sorry Ali, didn't want to make anyone upset. I will continue with what I have and leave the forum alone.
Thanks for your time.

Devon

In those particular cases in Post #13, you could use this approach:

=LEFT(A1,LEN(A1)-5)

Hope this helps.

Pete