import data based on matching criteria

[jamminz1]

I have 2 spreadsheets:

Spreadsheet 1 has column headers labeled LAST, FIRST, STATUS AND BADGE ID. Columns LAST and FIRST are populated with last names and first names respectively. TYPE and BADGE ID are blank.

Spreadsheet 2 has column headers labeled LAST, FIRST, STATUS AND BADGE ID. Columns LAST and FIRST are populated with last names and first names respectively. TYPE is populated with text and BADGE ID is populated with unique numbers.

Spreadsheet 2 has 1900 rows of data and Spreadsheet 1 has 600+. I need to add the TYPE and BADGE data from spreadsheet 2 to the matching LAST and FIRST of spreadsheet 1.

This is driving me crazy. Please help!!

[Ace_XL]

Good day,

Use a helper column and then a simple MATCH & INDEX function to help drive ur exasperation away!

In Sheet 1, in say Column E (helper column) combine reults of column LAST & column FIRST by using the following

Please Login or Register  to view this content.

where Column A is Column LAST and Column B is Column FIRST. This will give you a unique combination of last and first names.

Then on Sheet 2, use the following

=INDEX(Sheet1!C:C,MATCH(CONCATENATE(A1,B1),Sheet1!E:E,0)) to pull up STATUS and
=INDEX(Sheet1!C:C,MATCH(CONCATENATE(A1,B1),Sheet1!E:E,0)) to pull up BADGE ID

Show your appreciation - Press the star icon if this post has been helpful