Forum Discussion

SusanJackson's avatar
SusanJackson
Copper Contributor
May 01, 2023

Xlookup multiple match criteria

Hi - I would like to use a look up table to return an exact match based on multiple criteria (like an AND nesting in an IF function).  My data does not have a single key so I need to match 3 different criteria to return the correct result.  I am trying to nest multiple XLOOKUP criteria but can't seem to get it to work.  Example attached with fake data - I would like to return the right price increase depending on the three criteria:  brand, part type and subID.  All need to match to return the price increase.  Is this possible with an XLOOKUP or is there a better way to do this?

 

Formula:  =XLOOKUP(A2,'LookUp Table'!A1:A9,XLOOKUP(B2,'LookUp Table'!B1:B9,XLOOKUP(C2,'LookUp Table'!C1:C9,'LookUp Table'!D1:D9,none,0)))

 

Data Table

BrandPart TypeSub IDCurrent PricePrice Change ($)
RegularWidgetA10#VALUE!
RegularWidgetB25#VALUE!
RegularBoxA5#VALUE!
RegularBoxB10#VALUE!
SupremeWidgetA15#VALUE!
SupremeWidgetB30#VALUE!
SupremeBoxA15#VALUE!
SupremeBoxB50#VALUE!

 

LookUp Table

BrandPart TypeSub IDPrice Change ($)
RegularWidgetA2
RegularWidgetB3
RegularBoxA1
RegularBoxB5
SupremeWidgetA10
SupremeWidgetB15
SupremeBoxA20
SupremeBoxB0
  • SusanJackson 

    Traditional methods also include concatenation of the multipart keys to identify first or final occurrences of a matching record and the use of SUMIFS to aggregate over all matching records.

     

    That said, 365 will permit you to write your own variant of XLOOKUP which is designed to work with multipart primary keys. 

    XLookupλ
    = LAMBDA(selectedKey, primaryKey, returnArray,
        XLOOKUP(TRUE, BYROW(primaryKey, LAMBDA(key, AND(key = selectedKey))), returnArray)
      )

    BYROW tests for a match of all 3 parts of the key before moving to the next record.  In order to use the function for multiple lookups a further application of BYROW is required

    Worksheet formula
    = BYROW(foreignKey, LAMBDA(selectedKey,
        XLookupλ(selectedKey,primaryKey,PriceChangeSource)
      ))

     It is possible to wrap that in a further Lambda to be able to lookup the multiple records using a single function.  The formulas are more complicated that the traditional spreadsheet formula but the plus side is that once written they may be reused throughout the workbook and transferred to new workbooks rather than being rebuilt from scratch.

    • SusanJackson's avatar
      SusanJackson
      Copper Contributor
      Thank you Peter - I think this will work with the model too and be more robust as we build it out.
  • SusanJackson 

    I'd use INDEX+XMATCH:

     

    =INDEX('Lookup Table'!$D$2:$D$9, XMATCH(1, ('Lookup Table'!$A$2:$A$9=A2)*('Lookup Table'!$B$2:$B$9=B2)*('Lookup Table'!$C$2:$C$9=C2)))

    • SusanJackson's avatar
      SusanJackson
      Copper Contributor
      Thank you Hans - this is very simple and works well for this first pass model
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    SusanJackson You may try FILTER() function.

    =FILTER($L$2:$L$9,($I$2:$I$9=A2)*($J$2:$J$9=B2)*($K$2:$K$9=C2))

    For dynamic spill array use FILTER() with MAP() function.

    =MAP(A2:A9,B2:B9,C2:C9,LAMBDA(x,y,z,FILTER(L2:L9,(I2:I9=x)*(J2:J9=y)*(K2:K9=z))))

    See the attached file.

     

    • SusanJackson's avatar
      SusanJackson
      Copper Contributor
      Thank you Harun! this is also a simple way for the test model. I was able to use the FILTER() formula and modify it to look at the look up table in another worksheet, however, I received a SPILL error when using FILTER () and MAP () to reference the LookUp table in another worksheet though. I am a basic user so any feedback is welcome on this error

Resources