Vlookup on Two matches

[Peter Davison]

I have a Vlookup formula (See Below)
VLOOKUP($C26,Projection!$CQ$2:$ZZ$4998,MATCH($AR$7,Projection!$CQ$3:$ZZ$3,0),FALSE)
I want to use this formula above but also MATCH($C$3,Projection!$CQ$2:$ZZ$2,0)
So it has to match on both criterias
Can this be done
I believe it can be done with Index and Match, however I don't want it to return an array but just the first result it finds?
Any help would be appreciated.

 

[Cubist]

Can you post some sample data? It looks like you're matching up 2 header rows.

 

[Peter Davison]

First column is CQ
Row 3 is Fixture Linear
Row 4 is Cluster Number
So for example I am looking to -
Find Code 695362 (Row 10) in column CQ then match row 3 looking for a 12.0 in Row 3 and 8 in Row 4 where they match in the same column (DG)
This then returns me a value in Cell DG10 (184)
Hope I have explained that okay.
Thanks

New Sales
Store Number		101	102	104	105	108	109	110	112	114	118	119	120	121	122	123
Fixture Linear	All	8.00	12.00	9.00	9.00	9.00	8.00	12.00	9.00	10.00	7.00	11.00	8.50	11.00	9.00	12.00
Cluster Number	All	3	7	7	5	3	1	8	9	8	7	7	6	8	7	8
Store Unit Sales %		1.12%	0.99%	0.76%	1.30%	0.91%	0.84%	1.08%	1.17%	1.14%	0.40%	0.90%	0.93%	0.83%	0.67%	1.12%
Sales Unit Ratio		1.09	1.12	0.86	0.99	0.89	0.74	0.86	1.00	0.91	0.45	1.02	0.89	0.66	0.76	0.90
Products Stocked	9037239	154	184	165	166	164	148	183	165	168	116	185	149	185	164	185
839528	32
801371	15
695362	15313	330	169	112	134	95	138	158	144	240		149	155	91	167	184
794247	13540	234	203	204	103	67	92	92	207	251		147	147	77	125	135
852666	23044	369	310	485	196	233	224	254	144	255	124	210	143	147	281	366
697654	22540	448	165	250	270	252	198	51	353	275	106	462	202	256	80	293
776050	34315	700	489	317	591	370	217	128	333	318	146	302	187	263	165	424
513987	22958	280	301	109	216	129	209	100	313	264	105	194	390	113	120	371
748534	21163	238	228	127	413	155	270	138	250	217		162	289	96	132	485
636921	28877	669	290	158	198	222	344	175	375	277	187	393	266	132	117	253
881984	21870	418	175	152	223	310	334	160	233	347		205	232	112	156	203
659679	59291	755	509	365	436	437	725	832	460	495	368	467	574	359	336	636
393635	45092	521	440	359	251	278	574	352	449	330	244	435	194	289	201	455
871103	15119	343	261	111	132	104	339	62	132	147		149	337	70	79	173
499250	19559	419	252	244	244	224	147	88	348	218		284	145	147	88	373
533701	1446		43					18	49			35		22		32
557697	4550		64	24	89	77		28	28	28		40		27	33	161
582883	4827		24	55	53	67		59	102	70		51	60	39	51	123
854170		Deletion	Deletion	Deletion	Deletion	Deletion	Deletion	Deletion	Deletion	Deletion		Deletion	Deletion	Deletion	Deletion	Deletion
519289	81269	1464	1239	586	645	471	1214	568	950	1002	284	1099	564	820	460	744
552933	49432	12	652	340	446	391	492	364	664	602	216	759	389	481	387	589

 

[Cubist]

The first result it finds should be CY correct? not DG.

 

[Alex Blakenburg]

You changed your row numbers from your original post, so I don't know how that impacts the use of AR7 & C3.
Here are some options:-

=XLOOKUP($C26,
                          Projection!$CQ$2:$CQ$4998,
                          XLOOKUP(1,(Projection!$CQ$3:$ZZ$3=$C$3)*(Projection!$CQ$4:$ZZ$4=$AR$7),Projection!$CQ$2:$ZZ$4998),
                           "")

=INDEX(Projection!$CQ$2:$ZZ$4998,
                  MATCH($C26,Projection!$CQ$2:$CQ$4998,0),
                  MATCH(1,(Projection!$CQ$3:$ZZ$3=$C$3)*(Projection!$CQ$4:$ZZ$4=$AR$7),0))

=VLOOKUP($C26,
                          Projection!$CQ$2:$ZZ$4998,
                          MATCH(1,(Projection!$CQ$3:$ZZ$3=$C$3)*(Projection!$CQ$4:$ZZ$4=$AR$7),0),
                          FALSE)

 

[Peter Davison]

Thank you for the above most helpful
I will use the Vlookup

This is my formula (which is part of a larger formula (I have adjusted the cells and rows to be correct -
IF(AND(VLOOKUP($C10,
Projection!$CQ$2:$ZZ$4998,
MATCH(1,(Projection!$CQ$4:$ZZ$4=$C$3)*(Projection!$CQ$3:$ZZ$3=AR$7),0),FALSE)="",
XLOOKUP(AS$7&$C10,'Master Data'!$BD$3:$BD$100000&'Master Data'!$E$3:$E$100000,'Master Data'!$F$3:$F$100000,0)>0),"Removed",

Appreciate your help and Thanks

 

[Alex Blakenburg]

You're welome. Glad we could help.