Forum Discussion
SusanJackson
May 01, 2023Copper Contributor
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 differen...
PeterBartholomew1
May 02, 2023Silver Contributor
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
May 02, 2023Copper Contributor
Thank you Peter - I think this will work with the model too and be more robust as we build it out.