lookup table using Horizontal and Vertical criteria

[fayez_MrExcel]

With the example table below, i want to put the appropriate value in C3 up to C6 if the criteria is met in the right table. The criteria is : it will match the value of A3 Ex in the right table then once it is located it will use the column "Up" or "Low" depends on the value of B3. Example: the value of C3 should be 4%, C4 will be 4%, C5 is 2% and C6 is 5%. help please

=====================================

Book1
	A	B	C	D	E	F	G	H
2	Rate	Criteria	Percentage			Rate	Up	Low
3	EX	Low				Ex	5%	4%
4	VG	Up				VG	4%	3%
5	G	Up				G	3%	2%
6	EX	Up				F	2%	1%
Sheet1

 

[Jonmo1]

=index($g$3:$h$6,match(a3,$f$3:#f#6,0),match(b3,$f$2:$h$2,0))

 

[Greg Truby]

=index($g$3:$h$6,match(a3,$f$3:#f#6,0),match(b3,$f$2:$h$2,0))

I reckon jonmo's fingers are still warmin' up... edit the "#" signs to be "$" signs and you should be good to go.

=index($g$3:$h$6,match(a3,$f$3:$f$6,0),match(b3,$f$2:$h$2,0))

 

[Jonmo1]

Doh!!!

 

[fayez_MrExcel]

Sir Why it has #Ref! error...thanks

 

[Jonmo1]

See the typo Mr. Truby pointed out...
=index($g$3:$h$6,match(a3,$f$3:#f#6,0),match(b3,$f$2:$h$2,0))
SHOULD BE
=index($g$3:$h$6,match(a3,$f$3:$f$6,0),match(b3,$f$2:$h$2,0))

 

[Greg Truby]

Did you see the comment on the needed edit? Did you change the "#" signs to "$" signs and you are still getting a #REF error?

 

[fayez_MrExcel]

yes sir i copy the correct one and it is still has #Ref!

=index($g$3:$h$6,match(a3,$f$3:$f$6,0),match(b3,$f$2:$h$2,0))

 

[Jonmo1]

Ahh, found another problem...Sheesh is it that early??
I guess this is what I get for not testing...

=index($g$3:$h$6,match(a3,$f$3:$f$6,0),match(b3,$f$2:$h$2,0))
should be..
=INDEX($G$3:$H$6,MATCH(A3,$F$3:$F$6,0),MATCH(B3,$G$2:$H$2,0))

 

[Greg Truby]

Well, I had to put together a test sheet and get the #REF! error myself before I figured it out. And yes that is the key edit to make, change the "F" to a "G" in the second MATCH():

=INDEX($G$3:$H$6,MATCH(A3,$F$3:$F$6,0),MATCH(B3,$G$2:$H$2,0))