# lookup table using Horizontal and Vertical criteria

This is a discussion on lookup table using Horizontal and Vertical criteria within the Excel Questions forums, part of the Question Forums category; With the example table below, i want to put the appropriate value in C3 up to C6 if the criteria ...

1. ## lookup table using Horizontal and Vertical criteria

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
 C3 =

A
B
C
D
E
F
G
H
2
RateCriteriaPercentage RateUpLow
3
EXLow Ex5%4%
4
VGUp VG4%3%
5
GUp G3%2%
6
EXUp F2%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.

2. ## Re: lookup table using Horizontal and Vertical criteria

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

3. ## Re: lookup table using Horizontal and Vertical criteria

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

Doh!!!

5. ## Re: lookup table using Horizontal and Vertical criteria

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

6. ## Re: lookup table using Horizontal and Vertical criteria

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

7. ## Re: lookup table using Horizontal and Vertical criteria

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

8. ## Re: lookup table using Horizontal and Vertical criteria

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

9. ## Re: lookup table using Horizontal and Vertical criteria

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

10. ## Re: lookup table using Horizontal and Vertical criteria

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

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•