Excel matching separate first and last name columns to another sheet containing a combined "last, first name" column.

1
votes

I have searched and tried various VLOOKUP and MATCH formulas for matching separate first and last name columns to another sheet containing a combined "last, first name" column. I'm new to excel formulas like this.

Sheet 1 Column A is Last Name Column B is First Name

Sheet 2 Column G is Last Name, First Name Also in Column H there is an additional Last Name, First Name that I'd like to search for matches also if possible.

I'd like to find which names from Sheet 1 match data on Sheet 2.

Thank You!

2
excelexcel-formulaexcel-2010

2 Answers

2
votes

You should always post the code that you tried so that people don't feel like they're just doing your work for you. The following assumes you have no excess white-space in any cell and your letter casing matches between data. It also assumes that your second sheet is, indeed, named "Sheet 2". This belongs in the first row of your lookup column on Sheet 1

=IF(AND(ISERROR(VLOOKUP(A1&", "&B1,Sheet2!G:G,1,FALSE)),ISERROR(VLOOKUP(A1&", "&B1,'Sheet 2'!H:H,1,FALSE))),"NO MATCH",IF(ISERROR(VLOOKUP(A1&", "&B1,'Sheet 2'!G:G,1,FALSE)),"MATCH IN COL H","MATCH IN COL G"))
0
votes

If you use COUNTIF you can search both columns in one go, e,g. this formula in sheet1 C2 copied down

=IF(COUNTIF(Sheet2!G:H,A2&", "&B2),"Match","No Match")