Very simple and easy question: - How to use Indirect Function + Xlookup with 2 Criteria?

[Lacan]

Hello Guys,

Without VBA, how to use Indirect Function + Xlookup with 2 Criteria?
With the reference month in cell in C3 and reference D7 want to fill data in range C9:J9 accordingly.
In order to help just gave in the sheet 2 different examples accordingly what it has to go in column C8:J9.
All the help is highly appreciated.
Any question feel free to ask

Can you give a hand?
Thank you very much.

Indirect Function + Xlookup_V3.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2
3		JAN
4
5
6
7			299 Lx - Amadora (MÁgua - Sul)								hmR Region Values	MTH Rank PRD	MTH Rank MKT	MTH Rank MS	Market MTH Feb-23	Abc MTH Feb-23	Market PYG MTH %	Market PYG MTH abs	Abc PYG MTH %	Abc PYG MTH abs	Abc MS MTH Feb-22	Abc MS MTH Feb-23	Abc Var MS (p.p.) PY MTH
8											TOTAL				5 853 190	112 454	2,3%	133 222	3,4%	3 674	1,90%	1,92%	0,02
9											299 Lx - Amadora (MÁgua - Sul)	14	25	5	108 780	3 334	-2,0%	-2 228	-8,2%	-298	3,27%	3,06%	-0,21
10											300 Lx - Amadora (MÁgua - Norte)	22	26	16	107 519	1 910	-2,8%	-3 116	-1,4%	-28	1,75%	1,78%	0,03
11											301 Lx - Amadora (Venteira)	15	4	23	281 953	3 321	8,2%	21 328	-8,3%	-299	1,39%	1,18%	-0,21
12		*Accordingly with JAN in cell C3 and with 299 Lx - Amadora (MÁgua - Sul) in cell D7 the results should be									302 Lx - Amadora (Alfragide, ÁLivres)	6	8	11	251 005	5 776	1,3%	3 210	25,2%	1 163	1,86%	2,30%	0,44
13											303 Lx - Amadora (Falagueira, VNova)	23	27	15	103 314	1 861	5,4%	5 308	8,8%	151	1,74%	1,80%	0,06
14			299 Lx - Amadora (MÁgua - Sul)								304 Lx - Amadora (ESol)	10	16	14	190 352	3 846	1,9%	3 458	18,1%	590	1,74%	2,02%	0,28
15		Market MTH Jan-23	Abc MTH Jan-23	Market PYG MTH %	Market PYG MTH abs	Abc PYG MTH %	Abc PYG MTH abs	Abc MS MTH Jan-23	Abc Var MS (p.p.) PY MTH		308 Lx - Lisboa (Benfica - Sul)	9	24	4	110 832	3 966	2,1%	2 253	-13,3%	-607	4,21%	3,58%	-0,63
16		117 334 €	3 672 €	-1,1%	-1 253 €	0,8%	30 €	3,13%	0,06		309 Lx - Lisboa (Benfica - Norte)	5	2	13	307 437	6 320	8,5%	24 077	21,6%	1 124	1,83%	2,06%	0,22
17											310 Lx - Lisboa (Estrela)	26	22	20	112 740	1 553	3,5%	3 822	8,2%	117	1,32%	1,38%	0,06
18											311 Lx - Lisboa (COurique - Oeste)	29	13	33	197 971	1 108	8,0%	14 749	2,2%	24	0,59%	0,56%	-0,03
19											312 Lx - Lisboa (COurique - Este)	33	32	29	60 669	465	16,8%	8 728	11,7%	49	0,80%	0,77%	-0,03
20		*Accordingly with FEB in cell C3 and with 299 Lx - Amadora (MÁgua - Sul) in cell D7 the results should be									313 Lx - Lisboa (Misericórdia)	19	28	9	97 695	2 345	19,2%	15 706	50,0%	782	1,91%	2,40%	0,49
21											314 Lx - Lisboa (SAntónio - Oeste)	28	29	21	94 919	1 274	9,6%	8 352	0,5%	6	1,47%	1,34%	-0,12
22			299 Lx - Amadora (MÁgua - Sul)								315 Lx - Lisboa (SAntónio - Este)	21	31	6	67 873	1 932	-3,4%	-2 356	-35,6%	-1 067	4,27%	2,85%	-1,42
23		Market MTH Feb-23	Abc MTH Feb-23	Market PYG MTH %	Market PYG MTH abs	Abc PYG MTH %	Abc PYG MTH abs	Abc MS MTH Feb-23	Abc Var MS (p.p.) PY MTH		316 Lx - Lisboa (SMMaior)	7	17	10	188 268	4 405	10,5%	17 847	-8,5%	-412	2,83%	2,34%	-0,49
24		108 780 €	3 334 €	-2,0%	-2 228 €	-8,2%	-298 €	3,06%	-0,21		320 Lx - Lisboa (SVicente)	27	30	17	74 890	1 284	6,7%	4 695	22,5%	236	1,49%	1,71%	0,22
25											321 Lx - Lisboa (Campolide)	32	33	27	58 015	558	1,5%	873	123,2%	308	0,44%	0,96%	0,52
26											331 Lx - Lisboa (SDBenfica - Sul)	3	11	3	207 065	8 093	-0,2%	-492	-7,7%	-672	4,22%	3,91%	-0,31
27											332 Lx - Lisboa (SDBenfica - Norte)	31	20	32	133 822	767	7,7%	9 604	-13,3%	-118	0,71%	0,57%	-0,14
28											333 Lx - Lisboa (Carnide)	12	10	18	240 164	3 678	10,5%	22 813	-31,9%	-1 726	2,49%	1,53%	-0,95
29											349 St - Seixal (Seixal, APPires, FFerro)	18	12	22	206 350	2 438	1,9%	3 847	51,3%	827	0,80%	1,18%	0,39
30											350 St - Seixal (Arrentela)	2	15	2	192 312	10 011	-4,2%	-8 451	-1,1%	-107	5,04%	5,21%	0,17
31											351 St - Seixal (Amora)	8	3	19	304 178	4 257	-4,5%	-14 244	-1,6%	-70	1,36%	1,40%	0,04
32											352 St - Seixal (Corroios)	4	6	8	271 395	6 534	-3,9%	-10 960	37,6%	1 786	1,68%	2,41%	0,73
33											353 St - Almada (Laranjeiro)	20	14	24	197 947	2 279	1,5%	2 904	23,6%	435	0,95%	1,15%	0,21
34											354 St - Almada (CPiedade)	13	18	12	155 346	3 414	8,1%	11 612	35,0%	885	1,76%	2,20%	0,44
35											356 St - Almada (Feijó)	16	21	7	120 139	3 207	-4,6%	-5 832	25,0%	642	2,04%	2,67%	0,63
36											357 St - Almada (Caparica, Trafaria)	1	19	1	138 442	11 573	-4,1%	-5 981	2,2%	246	7,84%	8,36%	0,52
37											358 St - Almada (CCaparica)	30	23	28	111 714	1 072	-1,2%	-1 309	-10,6%	-127	1,06%	0,96%	-0,10
38											359 St - Almada (ChCaparica, Sobreda)	25	5	31	280 391	1 728	11,0%	27 737	-33,1%	-854	1,02%	0,62%	-0,41
39											361 St - Setúbal (NSAnunciada, SLourenço, SSimão)	17	9	26	246 575	2 448	2,5%	6 065	-5,5%	-143	1,08%	0,99%	-0,08
40											362 St - Setúbal (SJulião, SMGraça)	24	7	30	259 765	1 861	-4,5%	-12 119	27,4%	401	0,54%	0,72%	0,18
41											363 St - Setúbal (SSebastião, GPAGuerra, Sado)	11	1	25	373 354	3 837	-4,8%	-18 677	12,5%	427	0,87%	1,03%	0,16
42
43
44											hmR Region Values	MTH Rank PRD	MTH Rank MKT	MTH Rank MS	Market MTH Jan-23	Abc MTH Jan-23	Market PYG MTH %	Market PYG MTH abs	Abc PYG MTH %	Abc PYG MTH abs	Abc MS MTH Jan-22	Abc MS MTH Jan-23	Abc Var MS (p.p.) PY MTH
45											TOTAL				5 706 014	119 039	5,6%	300 733	12,1%	12 859	1,96%	2,09%	0,12
46											299 Lx - Amadora (MÁgua - Sul)	12	24	5	117 334	3 672	-1,1%	-1 253	0,8%	30	3,07%	3,13%	0,06
47											300 Lx - Amadora (MÁgua - Norte)	15	23	9	121 470	2 898	4,7%	5 487	-2,9%	-87	2,57%	2,39%	-0,19
48											301 Lx - Amadora (Venteira)	11	3	21	316 023	4 166	8,9%	25 769	27,3%	893	1,13%	1,32%	0,19
49											302 Lx - Amadora (Alfragide, ÁLivres)	4	5	10	288 325	6 319	10,5%	27 470	15,9%	866	2,09%	2,19%	0,10
50											303 Lx - Amadora (Falagueira, VNova)	20	25	12	110 631	2 338	3,7%	3 991	36,7%	627	1,60%	2,11%	0,51
51											304 Lx - Amadora (ESol)	14	13	19	205 992	3 403	1,2%	2 428	7,6%	239	1,55%	1,65%	0,10
52											308 Lx - Lisboa (Benfica - Sul)	7	20	3	130 615	6 085	7,6%	9 175	0,5%	29	4,99%	4,66%	-0,33
53											309 Lx - Lisboa (Benfica - Norte)	5	2	16	322 667	6 318	7,0%	21 203	-3,9%	-259	2,18%	1,96%	-0,22
54											310 Lx - Lisboa (Estrela)	18	21	13	128 660	2 607	11,7%	13 469	78,0%	1 142	1,27%	2,03%	0,75
55											311 Lx - Lisboa (COurique - Oeste)	26	10	30	222 411	1 315	14,8%	28 642	-10,3%	-151	0,76%	0,59%	-0,17
56											312 Lx - Lisboa (COurique - Este)	31	30	28	68 656	559	25,1%	13 797	89,6%	264	0,54%	0,81%	0,28
57											313 Lx - Lisboa (Misericórdia)	23	26	17	109 463	1 963	29,6%	25 024	10,2%	181	2,11%	1,79%	-0,32
58											314 Lx - Lisboa (SAntónio - Oeste)	28	27	27	106 259	930	16,5%	15 083	-19,4%	-224	1,27%	0,88%	-0,39
59											315 Lx - Lisboa (SAntónio - Este)	17	28	6	90 136	2 661	30,2%	20 887	19,1%	426	3,23%	2,95%	-0,28
60											316 Lx - Lisboa (SMMaior)	8	14	7	202 735	5 810	19,3%	32 797	22,9%	1 082	2,78%	2,87%	0,08
61											320 Lx - Lisboa (SVicente)	25	29	14	89 514	1 814	13,7%	10 820	125,1%	1 008	1,02%	2,03%	1,00
62											321 Lx - Lisboa (Campolide)	30	31	24	64 198	632	12,4%	7 059	110,1%	331	0,53%	0,98%	0,46
63											331 Lx - Lisboa (SDBenfica - Sul)	3	8	4	241 379	10 357	12,0%	25 945	26,1%	2 145	3,81%	4,29%	0,48
64											332 Lx - Lisboa (SDBenfica - Norte)	29	17	31	154 017	750	16,6%	21 977	-10,5%	-88	0,63%	0,49%	-0,15
65											333 Lx - Lisboa (Carnide)	10	7	15	254 907	5 037	9,0%	21 022	14,1%	624	1,89%	1,98%	0,09
66											349 St - Seixal (Seixal, APPires, FFerro)	21	9	23	222 867	2 269	-1,6%	-3 537	66,1%	903	0,60%	1,02%	0,41
67											350 St - Seixal (Arrentela)	1	12	2	210 947	11 037	-1,9%	-4 039	20,5%	1 879	4,26%	5,23%	0,97
68											351 St - Seixal (Amora)	9	1	20	338 218	5 073	-5,6%	-20 128	-7,8%	-428	1,53%	1,50%	-0,04
69											352 St - Seixal (Corroios)	6	6	11	286 458	6 127	-7,1%	-21 736	75,9%	2 643	1,13%	2,14%	1,01
70											353 St - Almada (Laranjeiro)	19	11	22	220 609	2 409	-1,7%	-3 808	15,1%	317	0,93%	1,09%	0,16
71											354 St - Almada (CPiedade)	16	16	18	159 232	2 852	0,2%	278	24,8%	566	1,44%	1,79%	0,35
72											355 St - Almada (Almada, Cacilhas, Pragal)	24	15	25	198 549	1 844	-0,9%	-1 771	11,2%	186	0,83%	0,93%	0,10
73											356 St - Almada (Feijó)	13	19	8	140 610	3 569	3,8%	5 209	30,6%	836	2,02%	2,54%	0,52
74											357 St - Almada (Caparica, Trafaria)	2	18	1	153 100	11 000	-3,3%	-5 269	-17,2%	-2 280	8,39%	7,18%	-1,20
75											358 St - Almada (CCaparica)	27	22	26	121 480	1 125	-4,1%	-5 151	-8,6%	-106	0,97%	0,93%	-0,05
76											359 St - Almada (ChCaparica, Sobreda)	22	4	29	308 551	2 102	10,7%	29 894	-26,0%	-738	1,02%	0,68%	-0,34
77											362 St - Setúbal (SJulião, SMGraça)	27	6	31	302 471	1 546	0,3%	773	0,4%	7	0,51%	0,51%	0,00
78											363 St - Setúbal (SSebastião, GPAGuerra, Sado)	12	1	28	449 674	4 308	3,9%	16 845	15,7%	584	0,86%	0,96%	0,10
79
INDIRECT 2.0

Cells with Data Validation
Cell	Allow	Criteria
C3	List	=$A$3:$A$4

 

[DanteAmor]

Thanks for posting on the forum.

Check this:

varios 17mar2023.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3			JAN
4
5
6
7				299 Lx - Amadora (MÁgua - Sul)
8
9			117334.2	3672.34	-0.01056	-1252.6	0.00832	30.3	0.030712	0.031298
Hoja6

Cell Formulas
Range	Formula
C9:J9	C9	=VLOOKUP($D$7,OFFSET($L$1,MATCH("*"&$C$3&"*",$P:$P,0),0,10000,13),COLUMNS($C$8:C8)+4,0)

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

----- --

 

[Lacan]

Dear @DanteAmor

Thank you very much for all the help.
Apparently is working fine even if had to change range tables.
Accordingly with sheet examples initially would like also to fill with the above correct text matching.
Can you give a hand?
Thanks again.