Looking up a value based on 2 conditions

**gico1972** · 09-11-2007, 05:49 AM

I need help trying to return a value based on 2 conditions.

Based on the table below i need a formula to return the value where the Category (A,B,C,D) meet the percentage.

Eg if % is 5% (between 0% and 10%) and is a Catergory C then i want to return the value 1.0.

Think that this is quite an easy function however i just cannot get my head round it.

Appreciate any help on this.

--------------------- A - B - C - D
between 0% 10% 0.5 0.8 1.0 1.3
between 11% 20% 1.0 1.3 1.5 1.8
between 21% 30% 1.5 1.8 2.0 2.3
between 31% 100% 2.0 2.3 2.5 2.8

**NBVC** · 09-11-2007, 08:24 AM

If your table is in A1:E5, then try

=INDEX($B$2:$E$5,MATCH(H1,{0,11,21,31}),MATCH(H2,$B$1:$E$1,0))

where H1 houses percentage to look for and H2 houses column header (letter) to look for....

see attached for example.

**Dav** · 09-11-2007, 08:30 AM

if b1:e1 are the column headers,and a2=0%, a3=11%, a4=21%, a5=31%
and the data is in the range(b2:e5)

the column header in j2 and the % to look up in j3

=INDEX(B2:E5,MATCH(J3,A2:A5),MATCH(J2,B1:E1,0))

Does that help

Regards

Dav