Forum Discussion

RAZA's avatar
RAZA
Copper Contributor
Oct 02, 2025

Finding a "2 cell" name in anther sheet and copying data

 

Hello-

 

I'm looking for a formula to enter in column D of the Main Sheet. I would like to find the "Total" in sheet: 2023 for the name in cells A&B, and copy them into the Main Sheet. The names are in a different order in each sheet. I've tried XLOOKUP, but couldn't make it work.

Any ideas?

 

 

excel
Excel on Mac
Formulas and Functions

2 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Oct 05, 2025

    You can try XLOOKUP() in this way-

    =XLOOKUP(1,(A2='2023'!$A$2:$A$9)*(B2='2023'!$B$2:$B$9),'2023'!$F$2:$F$9,"")

    Or FILTER() function

    =@FILTER('2023'!$F$2:$F$9,('2023'!$A$2:$A$9=A2)*('2023'!$B$2:$B$9=B2),"")

    Spill version of formula-

    =MAP(A2:A9,B2:B9,LAMBDA(a,b,@FILTER('2023'!F2:F5000,('2023'!A2:A5000=a)*('2023'!B2:B5000=b),"")))

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 03, 2025

    Hi

    In your 'Main' sheet cell E2 then copy down:

    =XLOOKUP( A2 & B2, '2023'!A$2:A$9 & '2023'!B$2:B$9, '2023'!F$2:F$9,  "no match" )

    Assuming there's no space anywhere around '2023' in your other sheet name

    In case there would be any extra spaces in your 'Main' and/or '2023' sheets, columns [First Name] and [Last Name] use :

    =XLOOKUP( TRIM(A2 & B2), TRIM('2023'!A$2:A$9 & '2023'!B$2:B$9), '2023'!F$2:F$9,  "no match" )

     

Resources