vlookup a table with a value before a comma

[Dares2]

hi, I have a need to vlookup a surname in a list but the lookup range has both surname, comma then first name so eg vlookup(smith in a list that has smith,david and take the forth value along) Im not sure how to write this formula thanks.

 

[Fluff]

You can do that like

=VLOOKUP(X2&"*",A2:D100,4,0)

where X2 is the cell with the value to find.

 

[Peter_SSs]

I would suggest including the comma in the lookup value (Y2 formula) to avoid a possible incorrect result (Z2 formula)

21 05 03.xlsm
	A	B	C	D	E																		W	X	Y	Z
1																									post #3	post #2
2	jones, alan	1	4	7																				smith	9	8
3	smithson, anne	2	5	8
4	smith, david	3	6	9
VLOOKUP

Cell Formulas
Range	Formula
Y2	Y2	=VLOOKUP(X2&",*",A2:D100,4,0)
Z2	Z2	=VLOOKUP(X2&"*",A2:D100,4,0)

 

[Dares2]

I would suggest including the comma in the lookup value (Y2 formula) to avoid a possible incorrect result (Z2 formula)

21 05 03.xlsm
	A	B	C	D	E																		W	X	Y	Z
1																									post #3	post #2
2	jones, alan	1	4	7																				smith	9	8
3	smithson, anne	2	5	8
4	smith, david	3	6	9
VLOOKUP

Cell Formulas
Range	Formula
Y2	Y2	=VLOOKUP(X2&",*",A2:D100,4,0)
Z2	Z2	=VLOOKUP(X2&"*",A2:D100,4,0)

Thanks although I couldnt get this to work. May because this is the otherway round ie looking up eg smith within a lookup range of smith,peter

Name		Gross pf new	Fnights	Total
Baker, Annie		$1,000.00	4.00	=SUM(C5*D5)+VLOOKUP(A5&"*",C9:E11,2,FALSE)
		Baker	Blue	2237
		Bawden	Red	2273
		Smith	Green	2283

 

[Dares2]

You can do that like

=VLOOKUP(X2&"*",A2:D100,4,0)

where X2 is the cell with the value to find.

Not sure but couldnt get this to work

Name		Gross pf new	Fnights	Total
Baker, Annie		$1,000.00	4.00	=SUM(C5*D5)+VLOOKUP(A5&"*",C9:E11,2,FALSE)
		Baker	Blue	2237
		Bawden	Red	2273
		Smith	Green	2283

 

[Peter_SSs]

May because this is the otherway round

Yes, it appears that you described this the wrong way around in your original post. But it also looks like you have the wrong column referenced in the lookup range in your latest attempt.
Is below what you want?

BTW, I suggest that you

• Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• Investigate XL2BB for providing sample data to make it easier for helpers.

21 05 03.xlsm
	A	B	C	D	E
4	Name		Gross pf new	Fnights	Total
5	Baker, Annie		$1,000.00	4	$6,237.00
6
7
8
9			Baker	Blue	2237
10			Bawden	Red	2273
11			Smith	Green	2283
VLOOKUP (2)

Cell Formulas
Range	Formula
E5	E5	=C5*D5+VLOOKUP(LEFT(A5,FIND(",",A5)-1),C9:E11,3,FALSE)

 

[Dares2]

Yes, it appears that you described this the wrong way around in your original post. But it also looks like you have the wrong column referenced in the lookup range in your latest attempt.
Is below what you want?

BTW, I suggest that you

• Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• Investigate XL2BB for providing sample data to make it easier for helpers.

21 05 03.xlsm
	A	B	C	D	E
4	Name		Gross pf new	Fnights	Total
5	Baker, Annie		$1,000.00	4	$6,237.00
6
7
8
9			Baker	Blue	2237
10			Bawden	Red	2273
11			Smith	Green	2283
VLOOKUP (2)

Cell Formulas
Range	Formula
E5	E5	=C5*D5+VLOOKUP(LEFT(A5,FIND(",",A5)-1),C9:E11,3,FALSE)

thanks so much..worked a treat

 

[Peter_SSs]

thanks so much..worked a treat

You're welcome. Glad we could help. Thanks for the follow-up.