lookup table using Horizontal and Vertical criteria

**fayez_MrExcel** · Apr 14th, 2008, 10:44 AM

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

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

******** ******************** ************************************************************************>

Microsoft Excel - Book1

___Running: 12.0 : OS = Windows XP

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

=

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

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

**Jonmo1** · Apr 14th, 2008, 10:48 AM

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

**Greg Truby** · Apr 14th, 2008, 10:55 AM

Originally Posted by jonmo1

=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** · Apr 14th, 2008, 10:56 AM

Doh!!!

**fayez_MrExcel** · Apr 14th, 2008, 11:01 AM

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

**Jonmo1** · Apr 14th, 2008, 11:02 AM

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** · Apr 14th, 2008, 11:03 AM

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** · Apr 14th, 2008, 11:09 AM

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** · Apr 14th, 2008, 11:16 AM

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** · Apr 14th, 2008, 11:18 AM

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))