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

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, could you provide the sample data and the expected results manually entered in a form that we can copy from?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[CluaseTony]

Try this
=XLOOKUP($A11&$B11,$A$2:$A$6&$B$2:$B$6,XLOOKUP(C$10,$B$1:$E$1,$B$2:$E$6))

I add a extra row to test

179​

352​

1300​

2​

2600​

 

[DMfba]

Thank you Tony.

This works and solves the problem. I also found it was easier to shorten the look up array and return array to a single column. This gave me the same answer, it was just easier for me to see.

 

[DMfba]

Thank you Peter for letting me know, I did update the profile and make sure Xl2bb is added.

 

[Peter_SSs]

I also found it was easier to shorten the look up array and return array to a single column. This gave me the same answer, it was just easier for me to see.

So, what formula did you end up with?

 

[CluaseTony]

Thank you Tony.

This works and solves the problem. I also found it was easier to shorten the look up array and return array to a single column. This gave me the same answer, it was just easier for me to see.

No worries, this shows that XLOOKUP can join cells/columns together to look up just ensure the lookup array matches. although it is easier to prep the data first, sometimes that isn't possible, I normally call these cells, helper cells, just be careful about having to much duplication and making you files larger that they should be.

 

[DMfba]

Hi All,

Peter - Here is the formula that I used. I am also going to attempt to paste the XL2BB info as well.

The smaller range I commented about earlier: Store units formula example I used lookup value of J3 ("Store Units") and look up array of D3:D7 (the data).
In the formula Tony used was look up value of $B$1:$E$1 and look up array of $B$2:$E$6
Both formulas seem to work just fine. For me, this way was just easier to see/read when trouble shooting.


For store units = XLOOKUP($H4&$I4,$A$3:$A$7&$B$3:$B$7,XLOOKUP(J$3,$D$3,$D$3:$D$7),0)
For Store Cost = XLOOKUP($H4&$I4,$A$3:$A$7&$B$3:$B$7,XLOOKUP(K$3,$E$3,$E$3:$E$7),0)

Tony- to your point, yes it shows that xlookup join the cells/arrays (Product & Store Location in my case). One thing I didn't know that others might is that Xlookup is very slow on a large data set. That being said, if you are going to make a helper cell/column (Product & Store Location) Vlookup turned out to be faster. At the moment, the data size is around 500k row and growing.


Thank you to all.

Xlook Up Test Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Sample Data							XLOOKUP
2
3	Product	Store Location	Unit Cost	Store Units	Store Cost			Product	Location	Store Units	Store Cost
4	225	840	1275	4	$5,100			225	840	4	5100
5	225	352	1275	2	$2,550			225	352	2	2550
6	225	150	1275	1	$1,275			680	150	0	0
7	225	442	1275	9	$11,475			522	442	0	0
8								234	630	0	0
Sheet1

Cell Formulas
Range	Formula
J4:J8	J4	=XLOOKUP($H4&$I4,$A$3:$A$7&$B$3:$B$7,XLOOKUP(J$3,$D$3,$D$3:$D$7),0)
K4:K8	K4	=XLOOKUP($H4&$I4,$A$3:$A$7&$B$3:$B$7,XLOOKUP(K$3,$E$3,$E$3:$E$7),0)
E4:E7	E4	=D4*C4

 

[Peter_SSs]

Thanks for providing the formula information. I have some comments.

1.
The second XLOOKUP in your formulas does nothing since you are already hard-coding the column of data to do the lookup in.
The formulas below give exactly the same results with a single XLOOKUP.

22 09 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K
3	Product	Store Location	Unit Cost	Store Units	Store Cost			Product	Location	Store Units	Store Cost
4	225	840	1275	4	5100			225	840	4	5100
5	225	352	1275	2	2550			225	352	2	2550
6	225	150	1275	1	1275			680	150	0	0
7	225	442	1275	9	11475			522	442	0	0
8								234	630	0	0
Lookup

Cell Formulas
Range	Formula
J4:J8	J4	=XLOOKUP($H4&$I4,$A$3:$A$7&$B$3:$B$7,$D$3:$D$7,0)
K4:K8	K4	=XLOOKUP($H4&$I4,$A$3:$A$7&$B$3:$B$7,$E$3:$E$7,0)
E4:E7	E4	=D4*C4

2. I do not know what your full actual data could be like, but concatenating the column values directly like you have can easily lead to errors. Look at this example where cells J5:K5 contain incorrect results because the direct concatenation has made it appear that the two blue cells and the two yellow cells match when in fact they do not. It is much safer (& not much longer) to include a delimiter between the column values to avoid such possible errors. I have shown that in the column O & P formulas that now return correct results for row 5.

22 09 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Product	Store Location	Unit Cost	Store Units	Store Cost			Product	Location	Store Units	Store Cost		Product	Location	Store Units	Store Cost
4	225	840	1275	4	5100			225	840	4	5100		225	840	4	5100
5	22	5352	1275	2	2550			225	352	2	2550		225	352	0	0
6	225	150	1275	1	1275			680	150	0	0		680	150	0	0
7	225	442	1275	9	11475			522	442	0	0		522	442	0	0
8								234	630	0	0		234	630	0	0
Lookup (2)

Cell Formulas
Range	Formula
J4:J8	J4	=XLOOKUP($H4&$I4,$A$3:$A$7&$B$3:$B$7,$D$3:$D$7,0)
K4:K8	K4	=XLOOKUP($H4&$I4,$A$3:$A$7&$B$3:$B$7,$E$3:$E$7,0)
O4:O8	O4	=XLOOKUP($H4&"|"&$I4,$A$3:$A$7&"|"&$B$3:$B$7,$D$3:$D$7,0)
P4:P8	P4	=XLOOKUP($H4&"|"&$I4,$A$3:$A$7&"|"&$B$3:$B$7,$E$3:$E$7,0)
E4:E7	E4	=D4*C4

 

[DMfba]

Hi Peter,

Thank you for showing the example. I have not used xlookup in the past and you are pointing out some things I was not aware of. The information I found so far, when looking up data in a column based off two different inputs all show the need for 2nd xlookup formula. As your example points out, that doesn't seem to be the case. The formula you are showing is shorter and more clear.

2. Errors
The actual data vs the sample data is a bit different where the look up variables are both letters and numbers. I don't think i would run into the error you are showing but you do point out a potential error that I was over looking. The sample data I was intending to show (might not have made it clear as I thought) was that the same product will be sold from multiple locations. The sales data, in a separate file shows the units and Cost. I was trying find a way to look the units and cost without making a helper column.

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

DM