Multiple lookup?

  Dazza40 23:03 21 May 07


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 07

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 07

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 07


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 07

Are you specifying the fourth parameter in the VLOOKUP arguments?

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

  Dazza40 22:08 23 May 07

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 07

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 07

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 07

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 07

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 07

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



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

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Apple MacBook Pro with Touch Bar review

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…