Three Way Lookup with Power Query & XLOOKUP (Two-Way Lookup too). Excel Magic Trick 1810

Learn how to do a 3-way or 2-way lookup in the Excel worksheet with XLOOKUP or in Power Query (in Excel and Power BI) using UnPivot and Merge.
Topics:
1. (00:00) Introduction
2. (00:05) 3-way lookup in the Excel worksheet with XLOOKUP
3. (01:45) 3-way in Power Query (in Excel and Power BI) using UnPivot and Merge
4. (03:46) Summary, Closing, Video Links

[romulusmilea]

Excel...lent and PQ-lent ! I knew about possibility to join first and second arguments on XLOOKUP, this makes this function more powerful than previous mega-star function VLOOKUP. For first XLOOKUP you used in fact its internal "HLOOKUP", and this makes XLOOKUP even stronger. Greetings from Romania !

[darrylmorgan]

Boom!Great Class To Start 2023...Thank You Mike :)

[Cracktune]

both of your books are gold. thanks man

[mattschoular]

So simple yet so powerful. Thanks Mike

[vijayraghavanr]

First to view like and comment 🙏 Unending treasure trove

[thegaminglearning]

One can use this combination too for solving this situation.

=XLOOKUP(
1,
XLOOKUP(
L[[#Headers], [Washington]:[California]],
L[[Washington]:[California]]
)
)

Actually, I love this one more. With this one, one doesn't need to use single-row values one by one.

=XLOOKUP(
TRUE,
BYROW(S[@[Product]:[SR]] = L[[Product]:[SR]], LAMBDA(a, AND(a))),
XLOOKUP(
L[[#Headers], [Washington]:[California]],
L[[Washington]:[California]]
)
)

[billhladik]

Good stuff as usual. I played with a more generalized n-way lookup. Here you can have as many row or column headers as needed.

=LET(lookfor, G3:G5,
matrix, A2:E76,
grid, IF(ISNUMBER(MATCH(matrix, lookfor, 0)), 1, 0),
pick, MAKEARRAY(ROWS(grid), COLUMNS(grid),
LAMBDA(row, col,
LET(
vert, SUM(CHOOSECOLS(TAKE(grid, row), col)),
horz, SUM(CHOOSEROWS(TAKE(grid, , col), row)),
gcell, INDEX(grid, row, col),
mcell, INDEX(matrix, row, col),
IF(AND(gcell=0, horz>0, vert>0, horz+vert=COUNTA(lookfor)), mcell, "")))),
list, TOCOL(pick, 3),
FILTER(list, list<>"", ""))

Lookfor can be a list n headers to match in matrix and they can come from rows or columns. You do not need to specify where they are found.The intersection cells will be returned in a list. There might be just 1 or could be several.

[wayneedmondson]

Nice one MIke! Thanks for the multiple solutions. Thumbs up!!

[SyedMuzammilMahasanShahi]

Thanks amazing Mike for this EXCELlent video.

[renzz-oo]

Thanks Mike!! This helped alot. I took me more steps to accomplish what you just did, but this is so much more efficient!

[thenuking]

Super practical explanations, thanks so much!!!

[Softwaretrain]

Thanks, Amazing as always, but please don't forget old-school students :)
below is my suggestion:


Also for the new school students following formula is my suggestion:


Finally, to solve this issue by custom column in my lovely feature (Power Query), the M formula can be this:

=Table.ToList(Table.SelectColumns(Table.SelectRows(IdLookup, (x)=>[Product]=x[Product] and [SR]=x[SR]), [State])){0}

[chairatyuichamroensap]

Thanks very much for merge query used more than one colume.

[vishal.pandey]

I vote for power query over formula

It is easy and good when the data is large

Great video

Will help me in my reporting stuff when there are connections between two tables

Thanks for the video

[viktorasgolubevas]

Just another approach for exercise: ⇨ filter (find) Row by composite key; ⇩ select the appropriate Column

=INDEX( FILTER(L, (B10=L[Product])*(C10=L[SR])), XMATCH(D10, L[#Headers]))

[gnelly]

Could you use this as a way to utilize hierarchies? Meaning could you use to populate income statement based on different departments? Or even to pick which level of an income statement to go to ten thousand foot view, next level, next level?

[Reduce_Scan]

The same way to solve in Clip with the addition MAP,

=MAP(S[Product], S[SR], S[State], LAMBDA(b, c, d,
XLOOKUP(b&c, L[Product]&L[SR],
XLOOKUP(d, D3:F3, L[[Washington]:[California]]))))

[rtrbs]

Thank you Sir.. Awesome ..I have a question when I tried to spill the result, It giving the wrong answer apart from the 1st cell.
Instead of using B10&C10 look up value and dragging it down. I've tried B10:B15&C10:C15.

[AndrewSeywright]

Awesome Mike you are a legend, will take a look at this Power Query solution this morning! Thanks for taking the time to make this video

[cjimmer]

Power query tends to unsort the original fact table sort order. Consequently, you have to add an index column to keep the original sorted order. I wonder why you lose the original sort in the fact table?