Multiple lookup?

  Dazza40 23:03 21 May 2007


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


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


=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


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....


  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....


  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?



  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

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



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

Elsewhere on IDG sites

Honor 9 Lite review

How Social Media has Propelled Political Graphic Design and Art in the Last Decade

The best kids apps for iPhone & iPad 2018

HomePod d’Apple : date de sortie, prix et fiche technique