lookup table using Horizontal and Vertical criteria

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
383
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
=index($g$3:$h$6,match(a3,$f$3:#f#6,0),match(b3,$f$2:$h$2,0))
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,013
=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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jun 19, 2002
Messages
10,013
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
Joined
Oct 29, 2005
Messages
383
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jun 19, 2002
Messages
10,013
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,099,055
Messages
5,466,305
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top