I was wondering if there is a functionality in XLookup that can return all valid entries when the look-up is done on more than one criteria. Does Xlookup have the capability to 'spill' like the filter function do? Below is an example: I would like to display all returns for the two parameters entered in cells E2 and F2, from the lists in columns A & B using Xlookup, is that possible? (I know it can be done with Match and Row, but it could be great if this can be done with Xlookup)
| Excel365.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Tax Jurisdiction | Tax Code | Amount | Tax Jurisdiction | Tax Code | Amount | |||
| 2 | NYA07200 | CN | 2,453.00 | NYA07300 | CR | (6,800.00) | |||
| 3 | NYA07200 | CN | 8,352.00 | (1,267.00) | |||||
| 4 | NYA07200 | CR | (63.56) | (7,612.00) | |||||
| 5 | NYA07300 | CN | 8,194.00 | 1,082.00 | |||||
| 6 | NYA07300 | CR | (6,800.00) | ||||||
| 7 | NYA07300 | CR | (1,267.00) | ||||||
| 8 | NYA07300 | CR | (7,612.00) | ||||||
| 9 | NYA07300 | EN | (6,393.00) | ||||||
| 10 | NYA07300 | EN | 1,429.00 | ||||||
| 11 | NYA07300 | EN | 4,121.00 | ||||||
| 12 | NYA07300 | CR | 1,082.00 | ||||||
| 13 | NYA07300 | EN | (4,246.00) | ||||||
| 14 | NYA07300 | EN | 725.00 | ||||||
| 15 | NYA07300 | EN | 684.00 | ||||||
| 16 | NYA07400 | ER | 5,489.00 | ||||||
| 17 | NYA07400 | ER | 5,508.00 | ||||||
| 18 | NYA07400 | O0 | (2,462.00) | ||||||
| 19 | NYA07400 | CR | 8,193.00 | ||||||
| 20 | NYA07400 | CN | (1,494.00) | ||||||
Sheet2 | |||||||||
