vlookup?

[JTL9161]

Within 1 spreadsheet I have 4 tabs. First tab (ALL) has approx. 200 names. Those names are listed once on the one of other 3 tabs/spreadsheets. What would be the formula on the ALL spreadsheet to look at the name in column A then search for that name on all of the other 3 tabs (where it should be listed on only 1 of the 3) and return the data in columns B, C and D. You can call the other tabs Tab1, Tab2, and Tab 3.

Appreciate your help.
Thank you,
James

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JTL9161]

I have updated my details. Excel 365 on Windows 10

 

[Fluff]

Thanks for that, how about in B2 dragged down only

=XLOOKUP(A2,Sheet1!$A$2:$A$25,Sheet1!$B$2:$D$25,XLOOKUP(A2,Sheet2!$A$2:$A$25,Sheet2!$B$2:$D$25,XLOOKUP(A2,Sheet3!$A$2:$A$25,Sheet3!$B$2:$D$25,"",0),0),0)

 

[JTL9161]

EXCELLENT! THANK YOU!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JTL9161]

While I have you.....

Next step with this. There are 4 Sheets. Sheet1(ALL), Sheet2, Sheet3, Sheet4. If any name is found on 2,3 or 4 it puts the data on Sheet1(All). Which is what we did above. On other sheets I have the master list of 200+ names. If name appears on for example Sheet2 how can that name be removed from master lists on Sheet3 and Sheet4?

 

[Fluff]

You cannot do that with a formula, it would need VBA

 

[JTL9161]

Thought so. Thanks.

 

[Fluff]

You're welcome.