# lookup table using Horizontal and Vertical criteria

#### fayez_MrExcel

##### Active Member
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
ABCDEFGH
2RateCriteriaPercentageRateUpLow
3EXLowEx5%4%
4VGUpVG4%3%
5GUpG3%2%
6EXUpF2%1%
Sheet1

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Jonmo1

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

#### Greg Truby

##### MrExcel MVP
=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))

Doh!!!

#### fayez_MrExcel

##### Active Member

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

#### Jonmo1

##### MrExcel MVP
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

##### MrExcel MVP

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

##### Active Member
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

##### MrExcel MVP
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

##### MrExcel MVP
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))

Replies
1
Views
89
Replies
2
Views
102
Replies
6
Views
509
Replies
4
Views
158
Replies
4
Views
112

1,129,472
Messages
5,636,515
Members
416,920
Latest member
Riskyplan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back