Modified Vlookup?

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Morning

I have this table present in a workbook
Generic Exchanges.xls
ABCDEFGHIJK
4Fee-EarnerLevelStartCaseloadTargetTotalExc%AchievedAgainstTargetFinishingCaseloadTotalComp
5JohuraHannon1115251768% 11119
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
hi,

Try:

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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")
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
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),"")

:) :) :) :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,908
Messages
5,525,577
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top