VLOOKLUP ERROR

[annadinesh]

Dear Expert

my combine formula of vlookup and hlookup is showing error

Please help

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2
3			EON	Santro	i10	New Santro	Grand i10 (P)	Grand i10 (D)	Xcent (P)	Xcent (D)	i20 (P)	i20 (D)
4		Engine Oil	925	1147	1332	1300	1332	1961	1332	1961	1332	1,961
5		Engine Oil Filter	107	107	107	107	107	467	107	467	107	467
6		Air Filter	168	417	246	210	222	198	222	198	332	332			Model	Part	Price
7		Ac Filter	410		410	217	410	410	410	410	932	932			New Santro	Engine Oil	#REF!
8		Coolant	400	400	400	400	400	400	400	400	400	400
9		Distilled Water	50	50	50	50	50	50	50	50	50	50
10		W/screen Washer	20	20	20	20	20	20	20	20	20	20
11		Bio Shampoo	11	11	11	11	11	11	11	11	11	11
12		Engine Flush	770	770	770	770	770	770	770	770	770	770
13		Engine Additive	780	780	780	780	780	780	780	780	780	780
14		Clutch Plate	1185	1260	1745	1500	1449	4845	1449	4845	5331	6286
15		Pressure Plate	804	1209	1595	981	1175	4446	1175	4446	4184	4248
16		Release Bearing	550	550	550	550	550	1263	550	1263	550	1263
17		Gear Oil	925	925	925	925	925	925	925	925	925	925
18
19
20
21
22
23
Sheet1

Cell Formulas
Range	Formula
Q7	Q7	=VLOOKUP(P7,B3:L17,HLOOKUP(O7,C3:L17,2))

Cells with Data Validation
Cell	Allow	Criteria
O7	List	=$B$3:$L$3
P7	List	=$B$3:$B$17

 

[arun_eagertolearn]

Hi

Please try

=VLOOKUP(P7,B3:L17,MATCH(O7,B$3:L$3,0),0)

 

[NickFru]

Hello, you can also either try if you so wish;

=INDEX(C4:L17, XMATCH(P7,B4:B17),XMATCH(O7,C3:L3)

Or

=OFFSET(B1,XMATCH(P7,B4:B17),XMATCH(O7,C3:L3))

 

[Fluff]

Excel 2019 does not have XMATCH.

 

[NickFru]

Ah OK Fluff - silly me !! - Annadinesh, ignore my suggestion re xmatch and replace with just match instead with, 0 after each match range if you so wish to you the formulas I've suggested.