Forum Discussion

sandraz869289's avatar
sandraz869289
Copper Contributor
Mar 10, 2023
Solved

XLOOKUP Formula Help needed in Excel

Help needed please...   I need to search for a name (i.e. Joe Smith) in columns V & X on one spreadsheet (see below) and have the CK NO on this spreadsheet be applied to another worksheet called Co...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Mar 10, 2023

    sandraz869289 

    Join the lookup columns and make the XLOOKUP run a wildcard match.

     

    For example (A1 being "Joe Smith")

    =XLOOKUP("*"&A1&"*",Sheet1!$V$2:$V$5&Sheet1!$X$2:$X$5,Sheet1!$A$2:$A$5,,2)

     

sandraz869289's avatar
sandraz869289
Copper Contributor
Apr 13, 2023

Patrick2788 You helped me a month ago when I did a February Commissions report and here I am again in March doing the same thing. The problem here is that the formula you gave me to use to show the commission summary for each sales person did not work. I have attached the file and the Green Tabs, is the problem I am having. What happened that I cannot get the information to populate with the formula you gave me? HELP! 

 

Thank you,
Sandra

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Apr 13, 2023

    sandraz869289 

    I created a fuction called GetData to pull the records. I figured if you're going to do this month-by-month might as well build something that can be used again.

     

     

    'GetData function - provide the range or named item containing the columns with the names for CommCab, SalesRep, and SalesRep2
=LAMBDA(Names_Columns,LET(records, BYROW(Names_Columns, PullRecords), FILTER(Receipts, records = "X")))

     

     

    All you have to do is this:

    Names  ='For Commission Receipts'!$V$2:$Z$1267

     

    =GetData(Names)

     

     

Resources