Excel- Pull Data from Multiple Sheets Based on Cell Criteria

Question

I have a sheet that has multiple tabs. One tab is a summary tab and on this sheet I would like to pull data from other sheets. This is the Summary Sheet.

Another tab is titled "Doubles".  Based on the name in Column A, I would like to pull the percentage in Column B and fill it in on the Summary Tab above for the correct person.  The important part is that it pull the data based on the name so if the names get out of order then the percentage is still for the correct person. 

There are additional sheets, but if I know how to pull from one sheet I can apply it to the others. Essentially there are a variety of sheets and I want to pull the score from each sheet but make sure to apply the percentage to the correct person.  Can anyone please advise on the formula I would use to pull this data from different sheets?  Thank you in advance!

Answer 1

In B2 on the Summary sheet:

=VLOOKUP(A2, Doubles!$A$2:$B$1000, 2, FALSE)

If the Doubles sheet has more than 1000 rows of data, increase the number 1000 in $A$2:$B$1000.

You can fill this formula down.

Answer 2

Thank you so much! It works perfectly!!!

Answer 3

The formula will match the name with column A on the Doubles sheet. The order of the names does not have to be the same. So if the name in A2 on the sheet with the formula is found in A37 on the Doubles sheet, the formula will return the value of B37 on the Doubles sheet.

Answer 4

I really appreciate all of your help!  I was able to get it to work on the example I used, but not on the actual sheet that I wanted to apply it to so I am going to post it a little differently.  I am not sure why I can't get the VLOOKUP to work....

I have a tab titled "Overview" and on this tab are student names and I want to pull the Date and % from different sheets, making sure to match up with the name. 

Overview Tab:

The first tab to pull from is titled "Copy of Week 1".  Based on the student name, from the "Copy of Week 1" tab I want to pull Column J into the "Overview" Tab, Column C.  Then from the "Copy of Week 1" tab I want to pull Column O into the "Overview" Tab Column D.

Copy of Week 1 Tab:

Essentially I want to pull two different columns from one tab into the main overview page, based on the name in Column A or B.

Thank you so much for any help!

Answer 5

I assume that you want to match on the combination of first and last name - there could be more than one Sally, and more than one Smith.

In C2 on the Overview sheet, enter the following formula and confirm it with Ctrl+Shift+Enter to make it an array formula:

=INDEX('Copy of Week 1'!$J$2:$J$1000,MATCH(A2&" "&B2,'Copy of Week 1'!$H$2:$H$1000&" "&'Copy of Week 1'!$I$2:$I$1000,0))

Format C2 as a date.

In D2, enter the following formula, also confirmed with Ctrl+Shift+Enter.

=INDEX('Copy of Week 1'!$O$2:$O$1000,MATCH(A2&" "&B2,'Copy of Week 1'!$H$2:$H$1000&" "&'Copy of Week 1'!$I$2:$I$1000,0))

Format D2 as a percentage.

Select C2 and D2, then fill down.