Xlookup 3 Way

[DMfba]

HI Everyone,

I am having some trouble doing a 3 way lookup using Xlook. I have two large tables of Product Inventor and Store sales. The goal is to bring in the sales information to the product inventory. The problem is each product can be ordered by multiple stores. To do this, I have to look up the each product, then the store , and find the sales info. Below is the xlookup formula that i am using and small shot of the the look up table. Currently, I am getting a error that say i am using the wrong value type. I am not exactly sure what part of the formula I have wrong or why. Any suggestions would be helpful.

XLOOKUP(A2,A2:A5,XLOOKUP(D1,$A$1:$E$1,XLOOKUP(B1,A1:E1,A2:E5,0,0),0))


Look up Table - Sales Info

Output that I am building

 

[CluaseTony]

All this is good stuff, I should point out that XLOOKUP is defaulted to always find an exact match, unlike VLOOKUP.
XLOOKUP can look to the left something VLOOKUP cannot do.
You don't always need a helper cell, its just sometime easier to have it.
Good luck with your coding

 

[Peter_SSs]

Thank you again for the additional formulas and pointing out things I overlooked.

You are very welcome.

XLOOKUP can look to the left something VLOOKUP cannot do.

I am definitely not recommending using it this way (much better to use XLOOKUP or INDEX/MATCH) but for the record, you can actually make VLOOKUP look left. Example

22 09 03.xlsm
	A	B	C	D	E
1	Amount	Name		Name	Amount
2	5	Name 1		Name 3	4
3	6	Name 2
4	4	Name 3
5	2	Name 4
Vlookup Left

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(D2,CHOOSE({1,2},B2:B5,A2:A5),2,0)