Forum Discussion

Pharmalisk's avatar
Pharmalisk
Copper Contributor
Dec 05, 2024

Xlookup using multiple criteria and itself

Hello, I am trying to automate a process of looking up values from two other sheets and I can't seem to get the last part down.

 

I have the working sheet and I am looking to match the Line Item from Sheet A and Sheet C. The criteria is: the line item must be between 50 and 100, and the department/line combo must be the same for Sheet A and Sheet C.

Looking at the Results Wanted, department 55678 has a line item 60 in both sheets. Department 999000 has line item 60 in Sheet A, but not in Sheet C, but it does have both for line item 100 and 100 is returned.

Which number it returns doesn't matter, so long as it matches.

My formula so far is =xlookup(1,(SheetADepartment Colum = Working Sheet Department)*(SheetALineItem Colum>=50)*(SheetALineItem Colum<=100),SheetALineItem Colum)

This formula is working, but I can't figure out how to also return something that is matching from Sheet C.

Thank you for the help!

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Dec 05, 2024

    In B2 on Working Sheet:

    =LET(filterA, FILTER('Sheet A'!$A$2:$A$100, 'Sheet A'!$B$2:$B$100=A2, ""),
     filterB, FILTER('Sheet B'!$A$2:$A$100, 'Sheet B'!$B$2:$B$100=A2, ""),
     combined, FILTER(filterA, ISNUMBER(XMATCH(filterA, filterB)), ""),
     INDEX(combined, 1))

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 05, 2024

    In B2 on Working Sheet:

    =LET(filterA, FILTER('Sheet A'!$A$2:$A$100, 'Sheet A'!$B$2:$B$100=A2, ""),
     filterB, FILTER('Sheet B'!$A$2:$A$100, 'Sheet B'!$B$2:$B$100=A2, ""),
     combined, FILTER(filterA, ISNUMBER(XMATCH(filterA, filterB)), ""),
     INDEX(combined, 1))

     

    • Mark_J_Walker's avatar
      Mark_J_Walker
      Brass Contributor
      Dec 05, 2024

      This does not include the filter for Item Numbers, but I'm sure you have worked out how to include that.


      =LET(filterA, FILTER('Sheet A'!$A$2:$A$100, ('Sheet A'!$A$2:$A$100>=50)*('Sheet A'!$A$2:$A$100<=100)*('Sheet A'!$B$2:$B$100=A2), ""),
     filterB, FILTER('Sheet B'!$A$2:$A$100, ('Sheet B'!$A$2:$A$100>=50)*('Sheet B'!$A$2:$A$100<=100)*('Sheet B'!$B$2:$B$100=A2), ""),
     combined, FILTER(filterA, ISNUMBER(XMATCH(filterA, filterB)), ""),
     INDEX(combined, 1))

       

    • Pharmalisk's avatar
      Pharmalisk
      Copper Contributor
      Dec 05, 2024

      Thank you, Hans!

      I was going about it the wrong way and this way worked great. I added an additional filter to filterA to limit the line item column to 50 to 100 and it worked perfectly!.

       

      Thank you again!

  • Mark_J_Walker's avatar
    Mark_J_Walker
    Brass Contributor
    Dec 05, 2024

    Hi, 

     

    See attached, which I hope works for you. I'm using the FILTER() function and also other array formulas

    The resulting formula is

    =LET(
dept,         selectedDept,
shtAItems,    SORT(FILTER(tbSheetA[ITEM],(tbSheetA[ITEM]>=50)*(tbSheetA[ITEM]<=100)*(tbSheetA[DEP]=dept))),
shtBItems,    SORT(FILTER(tbSheetB[ITEM],(tbSheetB[ITEM]>=50)*(tbSheetB[ITEM]<=100)*(tbSheetB[DEP]=dept))),
doItemsMatch, IFERROR(shtAItems=shtBItems,FALSE),
uniqueList,   UNIQUE(VSTACK(shtAItems,shtBItems)),
filterMatched, FILTER(uniqueList,doItemsMatch=TRUE),
IFERROR(TAKE(filterMatched,1),"Not Found")
)

    I have used tables for the SheetA and SheetB data as it's my pref. but not required.

    Formula Breakdown        
    Step1    Get a list of matching items in SheetA matching the Department Code AND where the ITEM>=50 and <=100
    Step2    Get a list of matching items in SheetB matching the Department Code AND where the ITEM>=50 and <=100   
    Step3    Compare the 2 lists, if they match we get a TRUE else FALSE    
    Step4    Build a unique list of items by combining the 2 lists    
    Step5    Filter the combined list by the step3 comparisons    
    Result    Take the first matching item if more than 1    




Resources