Forum Discussion
XLOOKUP return range reference instead of value in range
XLOOKUP does return an array - the first two formulas that I posted make use of that.
I don't think there is an essentially simpler way to do a two-way lookup.
Hi HansVogelaar thanks for your reply.
I have already tried XLOOKUP and INDEX MATCH. They can be used, but they make my formulas long and difficult to troubleshoot.
There is more to my situation: My table data has different versions, like below. The column month and row product may change across the different versions.
So far, I think the easiest way is to OFFSET(XLOOKUP( version , B:B, B:B )) to get the 2D array table out first. If version=ver1, table is B1:H6 If version=ver2, table is B8:H13. Then I need an inner XLOOKUP to get Mar + grape
I do have a solution with INDEX and nested XLOOKUP and XMATCH. I am exploring if I can simplify my formula, by getting XLOOKUP to return a range.
| A | B | C | D | E | F | G | H | |
| 1 | ver1 | Jan | Feb | Mar | Apr | May | Jun | |
| 2 | apple | 10 | 12 | 16 | 20 | 18 | 16 | |
| 3 | banana | 21 | 26 | 47 | 5 | 19 | 35 | |
| 4 | carrot | 48 | 28 | 30 | 45 | 2 | 4 | |
| 5 | grape | 28 | 5 | 9 | 33 | 1 | 9 | |
| 6 | kiwi | 39 | 25 | 28 | 11 | 41 | 3 | |
| 7 | ||||||||
| 8 | ver2 | May | Jun | Jul | Aug | Sep | Oct | |
| 9 | mango | 8 | 2 | 6 | 2 | 8 | 6 | |
| 10 | banana | 1 | 6 | 7 | 15 | 9 | 3 | |
| 11 | carrot | 28 | 8 | 10 | 4 | 3 | 9 | |
| 12 | grape | 10 | 15 | 8 | 4 | 21 | 11 | |
| 13 | kiwi | 19 | 3 | 4 | 7 | 9 | 4 |
XLOOKUP does return an array - the first two formulas that I posted make use of that.
I don't think there is an essentially simpler way to do a two-way lookup.