Multiple lookup?

  Dazza40 23:03 21 May 2007
Locked

Hi,

I have a table of surnames and first names. I would like to use Lookup to match names from another databse and when matched show a column from themain database.

I can use the Lookup function to match the surname and a result is made. The trouble is there are multiple common surnames e.g. Jones, Smith, Khan etc.

How do I mtach both surname and first name so that if both match to give a result?

i have tried Cncantenate but reult uis always #N/A.

Data is laid out in both spreadsheets as
ColA Colb
Surname First name

Thanks

  VoG II 07:17 22 May 2007

Using CONCATENATE (or simply &) seems a good idea but you need to ensure that there are no differences in the concatenated names in both databases - extra spaces in one would result in no match and a #N/A error.

You could use TRIM to remove surplus spaces, e.g.

=TRIM(A2 & " " & B2)

  daba 23:58 22 May 2007

I would extend VoG's excel..ent suggestion to include a correction in case a name was entered with incorrect capitalisation - like donald instead of Donald, or DONALD, oe even (incorrect use of caps lock), dONALD.

=PROPER(TRIM(A2 & " " & B2))

or

=TRIM(PROPER(A2) & " " & PROPER(B2))

Both constructs work equally well, although I suspect the former is more efficient, if not so easily understood.

Also if the names in the other D/B are written as Firstname/Lastname (like Donald Smith), then to correct the order the formula needs to be

=PROPER(TRIM(B2 & " " & A2))

  Dazza40 21:51 23 May 2007

Both,

Really strange. I've tried both of your excellent suggestions without success. I can match the surnames or first names but when I combine I still get #N/A even when I've created from scratch and typed the names in so I know they are tegh same.

  VoG II 21:55 23 May 2007

Are you specifying the fourth parameter in the VLOOKUP arguments?

=VLOOKUP(what, where, Col No, False)

  Dazza40 22:08 23 May 2007

Hi vog. Yes, really strange, I must be doing something wong. If i look for say A1 (surname) or B1 (first name) all is OK. When I look for C1 (combined) no match. Seems even if I type in data myself from scratch

  daba 22:26 24 May 2007

When you copy the vlookup formula down the column, you will be automatically adjusting the cell references for the "where" part of the vlookup.

try giving the whole vlookup range a name and using that in your vlookup formula, as in....

=vlookup(what,MyNames,ColNo,False)

  daba 22:39 24 May 2007

Could it also be that the data you are specifying for the "what" part has some leading or trailing spaces, or is incorrectly capitalised, if so you could correct for this also....

=VLOOKUP(PROPER(TRIM(what)),MyNames,2,FALSE)

  Dazza40 19:17 29 May 2007

Thanks for your suggestions. Unfortunately though it doesn't work. Again I've tried setting up in same spreadsheet on 2 seperate sheets. First names and surnames on both sheets are keyed in identically.
I still get N/A when looking for first name/surname although will match first name or surname.

A colleague has suggested Index and Match as a possibility. Any ideas if this would work?

Thanks

Daz

  daba 10:38 31 May 2007

Works for me OK. I have set up:-

ColA - Surname

ColB - Firstname

ColC = VLOOKUP( PROPER(TRIM(Firstname & " " & Surname)), Database, 2, False )


The Database is a named range I1:J12 :-

ColG - Surname

ColH - Firstname

ColI = PROPER(TRIM(ColH & " " & ColG))

ColJ - sequential number (data to extract)

Feel free to click my envelope and send me an example of what you are attempting

  Dazza40 21:47 11 Jun 2007

thanks,
I think I've been having one of those moments. works fine now.

Cheers


Dazza

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?