Forum Discussion
XLOOKUP return range reference instead of value in range
Hi everyone.
Summary
I would like to see if I can use XLOOKUP and intersection to do a cross reference / horizontal vertical lookup.
Detail
Let say my data (below) is in across rows and columns. Rows represent different months. Columns represent different products.
Goal: Get Mar + Grape, which is 9
Steps: To use intersection, I need to get range D1: D6 and range A5:G5.
I try to use XLOOKUP: I find the value grape in the range A1:A6 and return array of the whole data table. The result from XLOOKUP seems to be a range A5:G5, and showed up as the values from this range ie grape 28 5 9 33 1 9
Problem: In order to use intersection, I need the range reference A5:G5 rather than the values from A5:G5.
Is there a way for XLOOKUP to return the range reference or a formula to get the range reference so that I use it in intersection ?
=XLOOKUP("grape", A1:A6, A1:G6)
| A | B | C | D | E | F | G | |
| 1 | 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 |
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.
3 Replies
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.
