XLOOKUP to replace index match

[Cellardoor]

Dear All.

First, terribly sorry if this is a repeated question, but i couldn't find it anywhere

I use a lot the index match as follows:

=INDEX('Model '!C:C;MATCH('H10;'Model '!A:A;0)+2)

The '+2' in the end will return the value that is 2 positions after the "index" position.

Ex: If match is on row 4, the Index will return the value on the position 4 + 2...See my point?

It's like an OFFSET

Question: How can I do the same with XLOOKUP?

Thank you

 

[vletm]

Cellardoor
Is Your used formula correct? ...What is before H10?
About Index and Match could read from:

How to use INDEX and MATCH

INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you...

exceljet.net

There are examples.
About How Can I ... XLOOKUP ?
Can You send a sample Excel-file with some sample data?

 

[Cellardoor]

Cellardoor
Is Your used formula correct? ...What is before H10?
About Index and Match could read from:

How to use INDEX and MATCH

INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you...

exceljet.net

There are examples.
About How Can I ... XLOOKUP ?
Can You send a sample Excel-file with some sample data?

Sample data? Yes, of course.

Here it is

 

[Cellardoor]

The better answer i've found was a OFFSET(XLOOKUP("Ferrari";F:F;J:J;0);2;0).

But here, "if_not_found" doesn't work :s

 

[GraH - Guido]

Seems you got an answer on LinkedIn, no?

 

[Cellardoor]

Seems you got an answer on LinkedIn, no?

The answer I got was the one I've posted -> OFFSET(XLOOKUP("Ferrari";F:F;J:J;0);2;0).

(I don't know if there is a specific procedure when this happens)

Either way, the final answer, as far as I can get is this: "Continue to use index/match in the situation I've presented"

 

[Peter Bartholomew]

I do not think there is anything wrong with using INDEX/XMATCH rather than nested XLOOKUPs for this type of 2/3D data. XLOOKUP would be effective for looking up a column but you still need INDEX to pick out the row knowing the Make and Code.
A Lambda function that returns a data item given Make, Code and Month indices would be

DataByIndexλ = LAMBDA(makeIdx, codeIdx, monthIdx,
    INDEX(DataByMonth, 1 + codeIdx + 4 * (makeIdx - 1), monthIdx)
);

Since calling up the data by index, =DataByIndexλ(1,2,3), is somewhat obscure, this could wrapped to return the data by name

DataByNameλ = LAMBDA(make, code, month,
    LET(
        makeIdx, 1 + QUOTIENT(XMATCH(make, MakeTbl[Make]), 4),
        codeIdx, SWITCH(code, "Sales", 1, "Stock", 2, "Price", 3),
        mnthIdx, XMATCH(month, DataByMonth[#Headers]),
        DataByIndexλ(makeIdx, codeIdx, mnthIdx)
    )
);

which requires, =DataByNameλ("Ferrari","Stock","Mar"). It is possible to take this a step further by parsing your text descriptions "Ferrari Stock in March" etc.

DataByDescriptionλ = LAMBDA(description,
    LET(
        split, TRIM(TEXTSPLIT(description, " ")),
        make, INDEX(split, 1),
        code, INDEX(split, 2),
        month, LEFT(INDEX(split, 4), 3),
        DataByNameλ(make, code, month)
    )
);

which would be called using =DataByDescriptionλ(@required). To return and entire column of such descriptions as a dynamic array one would use
= MAP(required, DataByDescriptionλ)


Once your data exceeds 100 000 rows performance becomes more of an issue but I haven't had problems with Tables to that point.

 

[bosco_yip]

Hi,

1] It is not a good idea to wrap the XLOOKUP() with OFFSET(), INDEX+MATCH() is the best choice for your 2D Lookup example.

2] I advise to set up a database type Output table with header and the criteria all followed the Input data.

3] To find the Code position, you can add another MATCH(), e.g. MATCH(O7,G$8:G$10,0) as per below formula.

4] The formula in Q7, copied down :

=INDEX(H:K,MATCH(N7,F:F,0)+MATCH(O7,G$8:G$10,0),MATCH(P7,H$6:K$6,0))

Then,

5] It is a dynamic formula, you could change the criteria, and the result will be automatically changed.