XLookup using a calculated Array

[BaconMcSandwich]

I have an xlooup function I want to use however the lookup and return array's will chage. It might be that I want the lookup array to be A:A and the return array B:B. OR I want the lookup to be C:C and the return D:D.

I can calculate the columns I want to use (Either the alphabetic or numeric values) but is there a way to get my Xlookup to use the values I've calculated?

 

[JamesCanale]

MrExcelPlayground2.xlsm
	L	M	N
14		1	2
15	A	1	6
16	B	2	7
17	C	3	8
18	D	4	9
19	E	5	10
20
21	C	8
22	2
Sheet35

Cell Formulas
Range	Formula
M21	M21	=XLOOKUP(L21,L15:L19,OFFSET(M15:M19,,L22-1,5,1),"",0)

 

[BaconMcSandwich]

Hi,

Sort off, I think Offset might give me the return array, however for the lookup array you have still specified a value. If your table was repeated in the next 3 columns it might be that I want to lookup column L, or it might be that I want to lookup column O.

I can calculate the column I want to use, it's how to feed that into xlookup.

 

[Dave Patton]

Did you consider Choose it could select formula 1 or formula 2

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G
1	Animals	Bugs	Cars		Age	Weight	Height
2	Aardvark	Ant	Alero		1	10	2
3	Bear	Bug	Bugatti		2	20	3
4	Cat	Centipede	Camaro		3	30	5
5	Dog	Dragonfly	Dodge		4	40	7
6	Elephant	Earwig	Electric		5	50	11
7
8
9	Lookup	Result column	Value	Result
10	Cars	Height	Dodge	7
Sheet10

Cell Formulas
Range	Formula
D10	D10	=XLOOKUP(C10,INDEX(A2:C6,0,MATCH(A10,A1:C1,0)),INDEX(E2:G6,0,MATCH(B10,E1:G1,0)),"Not found",0)

 

[BaconMcSandwich]

Ultimately there could be 50+ different and search/return array pairs. Hence the reason I need to pick out the columns I need.

In my actual spreadsheet these tables are on seperate sheets, I've just put them together for ease.
Every week we will add new pair of ID's and finance rentals. I've calculated the week something was ordered based on the order date. But for that ID, in that Week I need to know the rate it was ordered at.
I will then be comparing the price it was ordered at to the latest week. Obviously the column that is the "latest" week will change also.

I've calculated that, for example week commencing is 7/6/21 is column C (lookup array) and that column D is the return array but it's just text values. I need to get XLookup to use it.

DataAnalysis.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	31/05/2021	Price	07/06/2021	Price				ID	Order Date	WeekOrdered	OrderRate
2	ID1	379	ID1	500				ID1	01/06/2021	31/05/2021
3	ID2	448	ID3	200				ID3	10/06/2021	07/06/2021
4	ID3	478	ID4	478				ID5	05/06/2021	31/05/2021
5	ID4	428	ID5	428				ID9	13/06/2021	07/06/2021
6	ID5	428	ID9	436
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=I2-(WEEKDAY(I2,2)-1)

 

[Eric W]

If your tables are on different sheets, then let's design the formulas that way, since it'll make a BIG difference in how they are created. How are your sheets named? Do they have a consistent format, like "31052021", "07062021", etc.? Are the tables in the same place on every sheet? Columns A:B for example? How many rows per table (more or less)?

 

[BaconMcSandwich]

I was intending 2 sheets. One with the main block of raw data (called "Data") where each week I add two new columns with that weeks ID's and their prices, last weeks wouund go in C and D, this weeks in columns E and F. The second sheet with the actual analysis comparing the rate when an item was ordered to the current week (called Orders).
The full list of ID and prices is about 50K rows.

Storing the weekly data in seperate sheets would make things easier in terms of the fact that I'd only be looking at columns A and B, however I'd need to reference a specific worksheet so I think it's the same problem just with the sheet name rather than the column's

I also can't really create a master list of ID's are new ones are addedd and some drop off, hence the idea of just looking at the speccific weeks. (I can deal with the error handling for that fine)

 

[BaconMcSandwich]

I was intending 2 sheets. One with the main block of raw data (called "Data") where each week I add two new columns with that weeks ID's and their prices, last weeks wouund go in C and D, this weeks in columns E and F. The second sheet with the actual analysis comparing the rate when an item was ordered to the current week (called Orders).
The full list of ID and prices is about 50K rows.

Storing the weekly data in seperate sheets would make things easier in terms of the fact that I'd only be looking at columns A and B, however I'd need to reference a specific worksheet so I think it's the same problem just with the sheet name rather than the column's

I also can't really create a master list of ID's are new ones are addedd and some drop off, hence the idea of just looking at the speccific weeks. (I can deal with the error handling for that fine)

So for ID1 the order rate would be 379. Currently I would need to compare it to the latest data (In column's C and D) and compare that price to the value of 500. However when I add this weeks data (for 14/6/21) the rate for ID1 may have changed and to (for example) £450 so I'm now comparing
Whilst ID3 is in both sets of data based on it's order date it should return the data from columns C and D, so 200

 

[Eric W]

2 sheets is actually better than many. Let's say your data sheet looks like this:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	31/05/2021	Price	07/06/2021	Price	14/06/2021	Price
2	ID1	379	ID1	500	ID5	111
3	ID2	448	ID3	200	ID4	222
4	ID3	478	ID4	478	ID3	333
5	ID4	428	ID5	428	ID2	444
6	ID5	428	ID9	436	ID1	555
Data

Just add your 2 new columns for new data.

Then you can use these formulas in your Orders sheet:

Book1 (version 1).xlsb
	A	B	C	D	E
1	ID	Order Date	WeekOrdered	OrderRate	6/14/2021
2	ID1	01/06/2021	31/05/2021	379	555
3	ID3	10/06/2021	07/06/2021	200	333
4	ID5	05/06/2021	31/05/2021	428	111
5	ID9	13/06/2021	07/06/2021	436	Not found
Orders

Cell Formulas
Range	Formula
E1	E1	=TODAY()-(WEEKDAY(TODAY(),2)-1)
C2:C5	C2	=B2-(WEEKDAY(B2,2)-1)
D2:D5	D2	=XLOOKUP(A2,INDEX(Data!$A:$ZZ,0,MATCH(C2,Data!$A$1:$ZZ$1,0)),INDEX(Data!$A:$ZZ,0,MATCH(C2,Data!$A$1:$ZZ$1,0)+1),"Not found",0)
E2:E5	E2	=XLOOKUP(A2,INDEX(Data!$A:$ZZ,0,MATCH(E$1,Data!$A$1:$ZZ$1,0)),INDEX(Data!$A:$ZZ,0,MATCH(E$1,Data!$A$1:$ZZ$1,0)+1),"Not found",0)

In E1 I calculate the most recent date I'd expect in the Data tab, the the E formulas simply use that value instead of the C value.