Xlookup formula with Retrun array referenced to a cell

[gpsaltus]

Hi All

I want to use xlookup instead of vlookup and the lookup array controlled by reference to a cell. for example in vlookup you can=VLOOKUP(J3,T:U,Q1,0) but in Xlookup =XLOOKUP($J2,$T:$T,U:U,0).

The reason is in the reports I am creating look at multiple pivot tables which have data by month. Using one cell you can update all formulas in one go.

Thanks guys

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this the sort of thing you mean?

23 07 30.xlsm
	J	K	L					Q			T	U	V	W
1								3
2											a	8	9	4
3	c	2	2								b	0.6	10	5
4											c	5	2	7
5											d	4	3	66
VLOOKUP v XLOOKUP

Cell Formulas
Range	Formula
K3	K3	=VLOOKUP(J3,T:W,Q1,0)
L3	L3	=XLOOKUP(J3,T:T,INDEX(T:W,0,Q1))

 

[gpsaltus]

Welcome to the MrExcel board!

Is this the sort of thing you mean?

23 07 30.xlsm
	J	K	L					Q			T	U	V	W
1								3
2											a	8	9	4
3	c	2	2								b	0.6	10	5
4											c	5	2	7
5											d	4	3	66
VLOOKUP v XLOOKUP

Cell Formulas
Range	Formula
K3	K3	=VLOOKUP(J3,T:W,Q1,0)
L3	L3	=XLOOKUP(J3,T:T,INDEX(T:W,0,Q1))

Thanks Peter but it didnt work, maybe cause I am referencing different sheets? I will stick to vlookup

 

[Peter_SSs]

I will stick to vlookup

Up to you, but referencing different sheets would not prevent using XLOOKUP if you want. You would just need to give us details of what you have, where it is, & clarify what you are wanting to do.