# Index/Match or Lookup using a partial string?

#### Collington

##### Board Regular
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:

but that returns "1" - also returns incorrect results for all the other rows

but that returns "0" - also returns "0" for the other rows

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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### RoryA

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### MrExcel MVP, Moderator
This is on a single sheet but does it do what you want?

21 06 10.xlsm
IJKLM
13Some text with EXS in it2PKF1
14EXS at the start21B1
15at the end is WTS11W1
16nothing here#N/A1S1
17abc 1S+ def11S+1
182B1
192W1
20WTS1
212S2
22EXS2
232s+3
24GDS3
Lookup Text
Cell Formulas
RangeFormula
J13:J17J13=LOOKUP(9^9,SEARCH(" "&L\$13:L\$24&" "," "&I13&" "),M\$13:M\$24)

#### Collington

##### Board Regular
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

##### MrExcel MVP, Moderator
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!

Replies
7
Views
121
Replies
3
Views
74
Replies
5
Views
32
Replies
1
Views
43
Replies
3
Views
328

1,141,130
Messages
5,704,454
Members
421,350
Latest member
jake9951

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back