Vlookup with Partial Text Match

[OkampoVictory]

Hello Everyone,

I am hoping to populate a cell in a worksheet using Vlookup and a lookup value. The lookup value is a First and a Last Name (e.g. Serena Williams). The table array is on a separate worksheet containing the staff details for all the staff in this company. However, the first and last name are on two separate columns (e.g. B1: Williams C1:Serena). The Vlookup formula I entered was not working. I have attached the example below:

Worksheet 1:
[TABLE="width: 376"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Staff
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD]Serena Williams
[/TD]
[TD](Want Result Here)
[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Surname
[/TD]
[TD]Forename
[/TD]
[TD]Position
[/TD]
[TD]Reporting Unit
[/TD]
[TD]Joining Date
[/TD]
[TD]Grade
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD]Williams
[/TD]
[TD]Serena
[/TD]
[TD]Manager
[/TD]
[TD]Finance
[/TD]
[TD]12/12/2012
[/TD]
[TD]B
[/TD]
[TD]Senior Manager
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Ideally, the Vlookup formula will return the rank based on part of the name present in worksheet 1.

Many thanks for your help!

Victory

 

[cooper645]

Which part of the name? first name or surname?

You will get errors when only partially checking and the same first name or surname appear. dependant on how you want to search

 

[OkampoVictory]

Hi Cooper645,

I am searching by the first name (Serena). Her name does not appear again on worksheet 2.

Thanks.

Victory

 

[FormR]

Hi, welcome to the board!

Here are a couple of options, the Option1 uses both the first name and the second, Option 2 just uses the first name.

Excel 2013/2016
	A	B	C
1	Staff	Option 1	Option 2
2	Serena Williams	Senior Manager	Senior Manager
3	Tim Henman	Team Leader	Team Leader
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX(Sheet2!$G$2:$G$1000,MATCH(A2,INDEX(Sheet2!$B$2:$B$1000&" "&Sheet2!$A$2:$A$1000,0),0))
C2	=VLOOKUP(LEFT(A2,FIND(" ",A2&" ")-1),Sheet2!$B$1:$G$1000,6,0)

Excel 2013/2016
	A	B	C	D	E	F	G
1	Surname	Forename	Position	Reporting Unit	Joining Date	Grade	Rank
2	Williams	Serena	Manager	Finance	12/12/2012	B	Senior Manager
3	Becker	Boris	Tea Boy	Catering	01/01/2001	A	Manager
4	Henman	Tim	Player	HR	01/01/2001	C	Team Leader
Sheet2

 

[OkampoVictory]

Hi FormR,

Amazing! Thanks for taking the time-much appreciated

Victory

 

[cooper645]

this should do the trick

=VLOOKUP(LEFT(A2,SEARCH(" ",A2,1)-1),Sheet2!B2:G13,5,0)



​Explained
=VLOOKUP(LEFT(A2,SEARCH(" ",A2,1)-1),Sheet2!B2:G13,5,0)

This looks in A2 and finds the space character and takes one from that number, essentially how long the first name is

this extracts the first name determined by the length found in red

The rest of the formula simply is the Vlookup for the entire first name from your data range on sheet 2.

 

[Simon4s]

Hello Everyone,

I am hoping to populate a cell in a worksheet using Vlookup and a lookup value. The lookup value is a First and a Last Name (e.g. Serena Williams). The table array is on a separate worksheet containing the staff details for all the staff in this company. However, the first and last name are on two separate columns (e.g. B1: Williams C1:Serena). The Vlookup formula I entered was not working. I have attached the example below:

Worksheet 1:
[TABLE="width: 376"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Staff[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Serena Williams[/TD]
[TD](Want Result Here)[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Surname[/TD]
[TD]Forename[/TD]
[TD]Position[/TD]
[TD]Reporting Unit[/TD]
[TD]Joining Date[/TD]
[TD]Grade[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Williams[/TD]
[TD]Serena[/TD]
[TD]Manager[/TD]
[TD]Finance[/TD]
[TD]12/12/2012[/TD]
[TD]B[/TD]
[TD]Senior Manager[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Ideally, the Vlookup formula will return the rank based on part of the name present in worksheet 1.

Many thanks for your help!

Victory

Assuming U have 5 rows of names, this will be the formula:

=INDEX(Worksheet2!G$2:G$5,MATCH(1,(LEFT(A2,(FIND(" ",A2)-1))=Worksheet2!B$2:B$6)*(MID(A2,FIND(" ",A2)+1,256)=Worksheet2!A$2:A$6),0))

 

[OkampoVictory]

Hi Cooper645,

Thanks for this-and the accompanying explanation too

Victory

 

[OkampoVictory]

Hi Simon4s,

Unfortunately I am a bit of a novice still with Excel but your solution gives me something to learn and understand. Thanks for your help!
Victory