Lookup with wildcards

Posted by Ian Mac on October 30, 2001 2:09 AM

All,

I'm having a nightmare, I have a list of logins for staff that are Last Name / First and SPH;

JanesP 0.96
HarveyM 0.45
HeppellL 1.01
llewellynJ 0.26
LakeyB 0.46
HillaryS1 0.88
BuchanE 1.12
BurnsJ 0.23

{"JanesP",0.957534349051193;"HarveyM",0.448737924587099;"HeppellL",1.01027108940899;"llewellynJ",0.255102040816327;"LakeyB",0.458054118245822;"HillaryS1",0.877567188737888;"BuchanE",1.12100641464782;"BurnsJ",0.231719876416066}

And staff Names on another sheet followed by monitored score:

Paul Janes 93%
Mark Harvey 74%
Lyn Heppell 63%
John Llewellyn 89%
Brian Lakey 45%
Sarah Hillary 38%
Ed Buchan 96%
Julie Burns 100%

{"Paul Janes",0.93;"Mark Harvey",0.74;"Lyn Heppell",0.63;"John Llewellyn",0.89;"Brian Lakey",0.45;"Sarah Hillary",0.38;"Ed Buchan",0.96;"Julie Burns",1}

in Column C: of the second sheet I need to do is look at the list on sheet1 and pull over the score. as you see some names will have a number after this because there are more than 1 person with that login (not bothered too much about having those scores as I'm trying to acheive a good cross section of result and 1 or 2 won't matter). I could use B#&Left(A#,1) BUT in a number of cases if the person as a middle name some all or none of the letters may be used, it will always be smithmarj or smithmj (if the middle name was mark say). The middle name never appears on the second sheet.

Hope thats not too confusing.

Any help would be great,

Thanks

Ian Mac

Posted by Aladin Akyurek on October 30, 2001 3:51 AM

Is it not:

=VLOOKUP(RIGHT(A1,LEN(A1)-SEARCH(" ",A1))&"*",Sheet1!$A$1:$B$8,2,0)

Aladin

============

Posted by Ian Mac on October 30, 2001 6:01 AM

=VLOOKUP(RIGHT(A1,LEN(A1)-SEARCH(" ",A1))&"*",Sheet1!$A$1:$B$8,2,0) Aladin ============ : All, : I'm having a nightmare, I have a list of logins for staff that are Last Name / First and SPH; : JanesP 0.96

Looks pretty good to me, do you happen to have a way round the 2 surnames being the same situation?

THanks Ian Mac

Posted by Aladin Akyurek on October 30, 2001 10:45 AM

> Looks pretty good to me, do you happen to have a way round the 2 surnames being the same situation?

Ian --

Do you mean by that [a] the same person or [b] 2 (or more) different persons who happen to have the same surname?

Aladin

Posted by Ian Mac on October 31, 2001 2:06 AM

Ian -- Do you mean by that [a] the same person or [b] 2 (or more) different persons who happen to have the same surname?

Two people with the same surname as in John Smith and John Smith being on the other sheet as SmithJ and SmithJ1.
. Aladin

I don't like picking things up after people realise that they should have used a different system a long time ago (An agentID I cried ages before now). Thats a whole different lookup.

I might throw it into access and give every a URN but was just wondering on your thoughts, I know you like the challange :).

P.S. By the way starting to expand the Quality Report you helped me on, I'll send you a copy (to doubtless rip apart) whan it's done, but it may a while.

Posted by Aladin Akyurek on October 31, 2001 1:05 PM

Ian -- I might throw it into access and give every a URN but was just wondering on your thoughts, I know you like the challange :).

Is it OK that I try to do that directly in your workbook before you turn to Access? P.S. By the way starting to expand the Quality Report you helped me on, I'll send you a copy (to doubtless rip apart) whan it's done, but it may a while.

Great. I'm interested.

Regards,

Aladin