Look up 2 values an return 2 results

[akalien]

Hi,

I need a formula to get two results depending on the search values of the Customer and the Revenue.

The Customer is any positive value but all values which are equal to 4 or greater are treated as 4.

The Revenue to be considered is within a range as shown in the table in column F.

So as in my example, if Customer is 2 and the Revenue is 3005 then the returned Price is 0,7 and the Bonus is 1.

Many thanks in advance

[Syrkrasi]

for Price (cell B7 insert formula below)

=IF(B4>15000,VLOOKUP(B2,G22:I25,2),IF(B4>10000,VLOOKUP(B2,G18:I21,2),IF(B4>3000,VLOOKUP(B2,G10:I13,2),IF(B4>0,VLOOKUP(B2,G6:I9,2),IF(B4<0,VLOOKUP(B2,G2:I5,2))))))

for Bonus (cell B9 insert formula below)

=IF(B4>15000,VLOOKUP(B2,G22:I25,3),IF(B4>10000,VLOOKUP(B2,G18:I21,3),IF(B4>3000,VLOOKUP(B2,G10:I13,3),IF(B4>0,VLOOKUP(B2,G6:I9,3),IF(B4<0,VLOOKUP(B2,G2:I5,3))))))

[bebo021999]

How about this, in B7:

=LOOKUP(B4,$E$2:$E$25,OFFSET($H$2:$H$25,MIN(B2,4)+3,))

B9:

=LOOKUP(B4,$E$2:$E$25,OFFSET($I$2:$I$25,MIN(B2,4)+3,))

[kev_]

Another way...

re-arrange your table, thus

2Matches.jpg

and with formula in A5

=INDEX($F$3:$J$8,MATCH(B3,E3:E8,1),MATCH(B1,F2:J2,1))

and formula in A7
=INDEX($F$3:$J$8,MATCH(B3,E3:E8,1),5)

[akalien]

Wow cool, many thanks to everybody!

Sorry for the late answer due to the fact that I was ill and not in office.

[kev_]

you are welcome
If your problem is solved please go to Thread Tools (top of thread) and mark the thread as "solved". thanks