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.
Let's say you enter grape in A8 and Mar in B8.
An XLOOKUP formula:
=XLOOKUP(A8,A2:A6,XLOOKUP(B8,B1:G1,B2:G6))
or reversing the lookups:
=XLOOKUP(B8,B1:G1,XLOOKUP(A8,A2:A6,B2:G6))
And an INDEX/XMATCH formula:
=INDEX(B2:G6,XMATCH(A8,A2:A6),XMATCH(B8,B1:G1))
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.
