Lookup question

[bquirk]

I have a spreadsheet with 2 columns. Simplified, it looks like this:

10% 100
20% 200
30% 300

Let's make believe it's a spreadsheet to calculate sales commission,
and the commission rate is based on a threshold value of sales. For
$250 in sales, for example, the commission would be 20%.

My goal is to create a statement that allows me to enter a number in a
separate cell (for example 250) from the conceivable range of values
in column B, that results in an output that relates to column A (20%).

What's critical is the following: The sales commissions need to be in
column B, and the output range needs to be in column A. From what I
know about VLookup, this is reverse from what's normal. Is this fatal?

Help! Thanks, Brendan

 

[Bernard Liengme]

Inconvenient but not fatal!
With percentages in A1:A10, dollar limits in B1:B10
With salesperson's amount in E1
This returns the percentage
=INDEX(A1:A10,MATCH(E1,B1:B10,1))
best wishes

 

[T. Valko]

I'm assuming your real figures don't follow the linear pattern that your
sample figures do.

D1 = 250

=LOOKUP(D1,B1:B3,A1:A3)

 

[muddan madhu]

try this

=INDEX(A1:A10,MATCH(C1,B1:B10,0)) ( format the cell in percentage )

 

[Bernard Liengme]

Maddan:
Respectfully, the last argument in MATCH needs to be 1 in this problem. A
zero value is used for an exact match
best wishes

 

[bquirk]

Gentlemen - This was exactly what I needed. It works beautifully. Many
thanks, Brendan

 

[Gord Dibben]

One method without using a table.

In A1 enter this formula

=B1*LOOKUP(B1,{0,200,300},{0.1,0.2,0.3})

Enter 250 in B1 to return 50 which is 20%

Any number below 200 will return 10% of B1

If 0 to 99.99 would be no commission.............

=B1*LOOKUP(B1,{0,100,200,300},{0,0.1,0.2,0.3})


Gord Dibben MS Excel MVP