Multiple lookups (vertical and horizontal)

[joand]

I need to do both horizontal and vertical lookups but dont know how to. I want to return any of the data from the range "I4:HC55" that meets the following criteria
Range I2:HC2 = "Greenwich"
Range I3:HC3 = "Sales"
Range F4:F55 = "02/10/2022"

Anyone want to offer some help

 

[ExceLoki]

try this:

=INDEX(I4:HC55,MATCH(5,F4:F55,0),MATCH(("Greenwhich")&("Sales"),(I2:HC2)&(I3:HC3),0))

 

[Sufiyan97]

Another Option

Products.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2				Greenwich					Other Text 1	Other Text 1	Other Text 1	Greenwich	Other Text 1
3				Sales					Other Text 2	Other Text 2	Other Text 2	Sales	Other Text 2
4						2/10/2022			72	23	53	27	38
5			2/10/2022	27		2/11/2022			75	61	14	73	61
6						2/11/2022			52	94	71	15	5
7						2/11/2022			33	38	39	39	80
8						2/11/2022			91	93	86	87	92
9						2/11/2022			66	96	97	45	34
10						2/11/2022			57	33	23	75	85
11						2/11/2022			38	63	58	35	69
Sheet5

Cell Formulas
Range	Formula
D5	D5	=INDEX($I$4:$HC$55,MATCH(C5,$F$4:$F$55,0),MATCH(D2&D3,$I$2:$HC$2&$I$3:$HC$3,0))

 

[ExceLoki]

=INDEX(I4:HC55,MATCH(5,F4:F55,0),MATCH(("Greenwhich")&("Sales"),(I2:HC2)&(I3:HC3),0))

you'd need to replace the "5" with the date you are looking for, and fix the spelling of Greenwich

 

[ExceLoki]

=INDEX(I4:HC55,MATCH(DATE(2022,2,10),F4:F55,0),MATCH(("Greenwich")&("Sales"),(I2:HC2)&(I3:HC3),0))

 

[Fluff]

What version of Excel are you using?
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’)