Modified Vlookup?

[MarkAndrews]

Morning

I have this table present in a workbook

Generic Exchanges.xls
	A	B	C	D	E	F	G	H	I	J	K
4	Fee-Earner	Level	StartCaseload	Target	TotalExc	%AchievedAgainstTarget				FinishingCaseload	TotalComp
5	JohuraHannon	1	115	25	17	68%				111	19
Marlice Figures

What i would like to do is have the Vlookups in column K read the first name in column A

Sample of the Vlookup

=VLOOKUP("Johura*",'Completion Results'!$E:$F,2,FALSE)

Johura appears in A5, can i incorporate left - The first names are all different lengths & seperaed from their surname by a space

Hope you can help

TIA

Mark

 

[fairwinds]

hi,

Try:

=VLOOKUP(LEFT(A5,FIND(" ",A5))&"*",'Completion Results'!$E:$F,2,FALSE)

 

[MarkAndrews]

Fairwinds, thankyou very much

 

[MarkAndrews]

Following from this

How can i remove the N/A IN K28

Generic Exchanges.xls
	K	L	M	N
4	TotalComp		Fee-Earner	Level
5	19		GwilymDavies	1
6	17		NasreenAkhtar	2
7	21		DanielleCruise	2
8	16		EstherYoung	2
9	15		JamieCruise	2
10	#N/A		JoanneCunliffe	3HW
11	19		SukhdevDhinsa	3HW
12	11		RachelGoacher	3HW
13	9
14	10		Totals
15	21
16	24
17	14
18	23
19	17
20	21
21	12
22	14
23	6
24	14
25	12
26	5
27	8
28	#N/A
Marlice Figures

TIA

 

[barry houdini]

Hi Mark,

either amend Fairwinds suggestion so that a blank (or some other text) is returned instead of #N/A. Then your SUM in K28 will work, i.e. use

=IF(COUNTIF('completion results'!$E:$E,LEFT(A5,FIND(" ",A5))&"*"),VLOOKUP(LEFT(A5,FIND(" ",A5))&"*",'completion results'!$E:$F,2,0),"")

or use this formula in K28 which will ignore #N/A errors

=SUMIF(K5:K27,"<>#N/A")

 

[patrickmuldoon99]

Can you not use an isna statement to catch the N/A included in the sum?

 

[MarkAndrews]

Hi Mark,

either amend Fairwinds suggestion so that a blank (or some other text) is returned instead of #N/A. Then your SUM in K28 will work, i.e. use

=IF(COUNTIF('completion results'!$E:$E,LEFT(A5,FIND(" ",A5))&"*"),VLOOKUP(LEFT(A5,FIND(" ",A5))&"*",'completion results'!$E:$F,2,0),"")

or use this formula in K28 which will ignore #N/A errors

=SUMIF(K5:K27,"<>#N/A")

Excellent, thanks Barry i used

=IF(COUNTIF('completion results'!$E:$E,LEFT(A5,FIND(" ",A5))&"*"),VLOOKUP(LEFT(A5,FIND(" ",A5))&"*",'completion results'!$E:$F,2,0),"")