Use a new column for both list and concatenate the first and last name together into a unique identifier for both lists (make sure this column is to the left of the address on that list). From there you can vlookup against the unique identifier you created to pull the address.

Google "vlookup with concatenated value"

I think the easiest way to do this would be to concatenate the first and last name together in a temporary field, and use that just for the vlookup purposes... basically you can either use the CONCATENATE function or more simply: A1 is Peter, B1 is Jones, C1 is =A1&B1, which should give you "PeterJones" in C1. Then do your vlookup based around that value; that should solve most of the issues unless you've got multiple individuals with the same first and last name.

As an aside, rather than vlookup, I'd suggest using the index and match functions... that way you don't have to have your lookup field always to the right of your initial array column. The syntax is =index({target column which contains the data that you want to return},match({column that contains the lookup data},{value that you want to find},false). For example =index(A:A,match(B:B,"peterjones",false)) would find "peterjones" in column B, then return the value in the same column as A. It gives you a LOT more flexibility in determining how you define your lookup... the "match" function can be used to search horizontally across rows/headers and return something from a vertical column, for example

+1

Bingo. Just concatenate the two unique identifiers. Should look like this: =CONCATENATE(A1,B1)

Do a vlookup of this concatenated cell, and it should help you filter it down like you need.

did anyone say to concatenate the strings yet?

Now if only there was some excel function that would make people write their names legibly, actually use there REAL names instead of abbreviations/middle names, and actually provide the master organization with addresses in the first place I'd be in business!

Only have 58 addresses out of 110!

As the registrar for the local tri club I went through this often for this and that. For a list that small, I would use last name only and do the duplicates by hand (or by eye).

So column has the normal vlookup on name, column 2 has the number of hits for the vlookup on last name, If it's 1 obviously you are golden. If it's more than 1 you need to look up which anderson it was by hand.

For added fun try the soundex function to try to get it to match things that it thinks should sound alike. But if the list is relatively small a manual pass over the results to pick out typos and nick name matching is usually quicker and more accurate.

Those are excellent functions. Thanks! Printing that out and tacking it to my wall now.

Or save yourself some keystrokes and use

=A1 & B1

---

king of the road says you move too slow
KING OF THE ROAD SAYS YOU MOVE TOO SLOW

This - create a new column with concatenated fname, space, lname and hide it. Then use in vlookup.

---

Next races on the schedule: none at the moment

I have used this tool and it will do what you want with a GUI and not need excel formulas:

https://www.springbok.is/


Load the two spreadsheets as CSVs into this thing and you can split/concatenate and merge columns however you want. It can also join two sheets based on a common key of your selection.


Full disclosure: My company makes this tool. Hope this is okay, let me know if posts like this are inappropriate and I will remove.