HLOOKUP with Multiple Criteria

[gldurand]

I am currently using a HLOOKUP to find a value within a table. But i have to change the Row reference each time for the position i am looking for in the table.
Does someone have a formula that I can insert in Cell L3 to replace my HLOOKUP.
I need to lookup BUCKET (Col H) and ROLE (Col B) and provide corresponding RATE without having to change the Row reference.

 

[Fluff]

Can you please post your current formula, rather than just an image.

 

[gldurand]

2022 Contractors Matrix.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
2	Resource Name	Role	Manager	Resource Type	Start date	End date	Tenure	Bucket	Adjustment	Contractor Rate	Contractor Rate	Matrix
3		Developer			28-Feb-22	Open Ended	0.12	0.5				$ 45.00
Roster

Cell Formulas
Range	Formula
G3	G3	=(TODAY()-E3)/360
H3	H3	=IF(G3<0.5,0.5, IF(G3<1,1, IF(G3<1.5,1.5, IF(G3<2,2, IF(G3<2.5,2.5, IF(G3<3,3, IF(G3<4,4,">4")))))))
L3	L3	=IF(I3<>"", HLOOKUP(I3,Tables!$B$16:$I$25,7,FALSE), HLOOKUP(H3,Tables!$B$16:$I$25,7,FALSE))

2022 Contractors Matrix.xlsx
	A	B	C	D	E	F	G	H	I
16	Bucket -->	0.5	1	1.5	2	2.5	3	4	>4
17	Role	< 6 months	6 to 1 yr	1 to 1,5 yrs	1,5 to 2 yrs	2 to 2,5 yrs	2,5 to 3 yrs	3 to 4 yrs	4+ yrs
18	BA/PM	$ 45.00	$ 47.50	$ 52.50	$ 57.50	$ 60.00	$ 65.00	$ 70.00	$ 75.00
19	Data Engineer	$ 45.00	$ 47.50	$ 52.50	$ 57.50	$ 62.50	$ 70.00	$ 77.50	$ 85.00
20	Data Hunters	$ 30.00	$ 30.00	$ 30.00	$ 30.00	$ 30.00	$ 30.00	$ 30.00	$ 30.00
21	Data Science	$ 45.00	$ 47.50	$ 52.50	$ 57.50	$ 62.50	$ 70.00	$ 77.50	$ 85.00
22	Developer	$ 45.00	$ 47.50	$ 52.50	$ 57.50	$ 62.50	$ 70.00	$ 77.50	$ 85.00
23	Project Coordinators	$ 45.00	$ 47.50	$ 52.50	$ 57.50	$ 62.50	$ 65.00	$ 70.00	$ 75.00
Tables

Cell Formulas
Range	Formula
B19:E19	B19	=B18
B21:E21	B21	=B18

 

[Fluff]

Thanks for that, how about

=IF(I3<>"",INDEX(Tables!$B$18:$I$25,MATCH(B3,Tables!$A$18:$A$25,0),MATCH(H3,Tables!$B$16:$I$16,0)),"")

 

[gldurand]

Thanks for that, how about

=IF(I3<>"",INDEX(Tables!$B$18:$I$25,MATCH(B3,Tables!$A$18:$A$25,0),MATCH(H3,Tables!$B$16:$I$16,0)),"")

Thanks for all your help, works like a charm

 

[Fluff]

You're welcome & thanks for the feedback.