Index/Match or Lookup using a partial string?

[Collington]

Hello you sexy nerds,

Ever feel like you're beating your head against a wall for something that should be so simple to do? That's today's fun and games....

I've got some kids' End of Year Predictions which I'm attempting to turn into a numerical value.

Kids' results:

Number to return based on the result:

As you can see I have one child whose results are complex and there's more text in the End of Year cell (i6). I need to be able to pull out the "EXS" portion of that text and match it to the Gradings sheet. (So the result should show as "2")

I've tried a few ways:

An array: {=IFERROR(INDEX(Grading!$D$1:$D$20,MAX((IFERROR(SEARCH(Grading!$C$1:$C$20,I16),-1))*(ROW(Grading!$C$1:$C$20)))),"No match")}
but that returns "1" - also returns incorrect results for all the other rows

Lookup: =LOOKUP(2,1/(ISNUMBER(SEARCH(Grading!C1:C20,Results!I16))),Grading!D1:D20)
but that returns "0" - also returns "0" for the other rows

Index/Match with wildcard: =INDEX(Grading!$C$1:$D$20,MATCH("*"&Results!I16&"*",Grading!$C$1:$C$20,0),2)
but that returns "#N/A" - however it does give me the correct result on cells that just have the pure result (e.g. EXS or GDS)

Clearly I'm doing something wrong. I suspect I keep making the same mistake, I just can't seem to see it! Hive mind - can you set me straight? I'm feeling like a fool here!

Thanks so much!!! ?

Lisa, the eternally grateful

 

[RoryA]

Assuming there's a space before the code and that the code is always at the end, it looks like you could just use the last 3 characters and then trim off any spaces (where the code is only 2 chars) so your VLOOKUP value is just TRIM(RIGHT(cell,3))

 

[Collington]

Assuming there's a space before the code and that the code is always at the end, it looks like you could just use the last 3 characters and then trim off any spaces (where the code is only 2 chars) so your VLOOKUP value is just TRIM(RIGHT(cell,3))

Good thought Rory, but it isn't always as tidy as being on the end. ?

 

[Peter_SSs]

This is on a single sheet but does it do what you want?

21 06 10.xlsm
	I	J	K	L	M
13	Some text with EXS in it	2		PKF	1
14	EXS at the start	2		1B	1
15	at the end is WTS	1		1W	1
16	nothing here	#N/A		1S	1
17	abc 1S+ def	1		1S+	1
18				2B	1
19				2W	1
20				WTS	1
21				2S	2
22				EXS	2
23				2s+	3
24				GDS	3
Lookup Text

Cell Formulas
Range	Formula
J13:J17	J13	=LOOKUP(9^9,SEARCH(" "&L$13:L$24&" "," "&I13&" "),M$13:M$24)

 

[Collington]

Oh Peter - you are good!! I have no idea what the "9^9" thing is - clearly this is my "learn something new every day" for today! I look forward to researching it. THANKS SO MUCH!!

 

[Peter_SSs]

THANKS SO MUCH!!

You're welcome. Thanks for the follow-up.

I have no idea what the "9^9" thing is

It is just a large number (9 to the power of 9) - just has to be larger than any of the SEARCH functions will return so if your strings in column I were never more than, say, 35 characters then that number really only needs to be bigger than 35. 9^9 is just a quick and easy way to write a fairly large number that will definitely be bigger than your longest text!