Index/Match or Lookup using a partial string?

Collington

Board Regular
Joined
Jun 6, 2005
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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:
results.png



Number to return based on the result:

grading.png


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
 

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
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 6, 2005
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
50,546
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

Collington

Board Regular
Joined
Jun 6, 2005
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
50,546
Office Version
  1. 365
Platform
  1. Windows
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! :)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top