How to lookup partial string match in Excel?
For example, there is a range of data, and column E is the students’ name list, column F and G are the class and grade of the students, and column H is the ranking of the students’ scores, now you have a given name “Jack” which is the partial string of “Jacky”, how can you quickly lookup partial string match and return the ranking of the “Jacky” as below screenshot shown.
Quickly select cells based on one or two criteria
|For instance, you want to select all values which begin with a particular string "Peac", just need to specify the criteria is Begins with and then enter the string into next textbox, click Ok or Apply, all values which begin with the string have been selected. You also can specify the criteria as contains, greater than, less than and so on. Click for 60 days free trial!|
|Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
Here I have some formulas to help you lookup partial string match in Excel.
1. Select a blank cell to enter the partial string that you want to look up. See screenshot:
2. Select another cell which you will place the look up value at, and enter this formula =VLOOKUP($K$1&"*",$E$1:$H$14,4,FALSE), press Enter key to get to value. See screenshot:
1. In the formula, K1 is the cell containing the partial string, E1:H14 is the data range, 4 indicates to lookup value in the Forth column of the range. You can change them as you need.
2. If you just want to find which name is partial match the given name, you also can use this formula =INDEX($E$2:$E$14,MATCH($K$1&"*",E2:E14,0)). (E2:E14 is the column list you want to lookup from, k1 is the given name, you can change as you need.)
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 4 months agoI can't thank you enough, the =INDEX($E$2:$E$14,MATCH($K$1&"*",E2:E14,0)) is a real lifesaver
- To post as a guest, your comment is unpublished.· 7 months agoI wish you would show us where to find it in Kutools. Great tool when you can find what you need
- To post as a guest, your comment is unpublished.· 7 months agoHi, KHill, so far, there is no a featrue in Kutools for Excel can lookup partial match, but soon, we will release some new features about vlook in new version of Kutools, you can try them then.