Look up formuls for excel

[turks393]

How do I have it look up the number entered on one sheet and if it is between two values then return teh number in the next cell?

 

[Gavona]

Have a look at vlookup in Help


Gavin

 

[turks393]

I tried to have it use vlookup, but didn't get the results I was looking for.

I have ranges (see below) on one sheet. On the another sheet I enter a $ amount (say $81.02) in one cell and I want the cell with the formula to read $5.00. If I enter 1,112.95 I want it to return $40.00.

Can you tell me what formula will be? Something like if(a1 is between 50 and 150 then 5, or between 150.01 and 300 then 10)>???

$50.00 - $150.00 = $5.00
$150.01 - $300.00 = $10.00
$300.01 - $450.00 = $15.00
$450.01 - $600.00 = $20.00
$600.01 - $750.00 = $25.00
$750.01 - $900.00 = $30.00
$900.01 - $1,050.00 = $35.00
$1,050.01 - $1,200.00 = $40.00
$1,200.01 - $1,350.00 = $45.00
$1,350.01 - $1,500.00 = $50.00
$1,500.01 - $1,650.00 = $55.00
$1,650.01 - $1,800.00 = $60.00
$1,800.01 - $1,950.00 = $65.00
$1,950.01 - $2,100.00 = $70.00
$2,100.01 - $2,250.00 = $75.00
$2,250.01 - $2,400.00 = $80.00
$2,400.01 - $2,550.00 = $85.00
$2,550.01 - $2,700.00 = $90.00
$2,700.01 - $2,850.00 = $95.00
$2,850.01 - $3,000.00 = $100.00
$3,000.01 - $3,150.00 = $105.00
$3,150.01 - $3,300.00 = $110.00
$3,300.01 - $3,450.00 = $115.00
$3,450.01 - $3,600.00 = $120.00
$3,600.01 - $3,750.00 = $125.00
$3,750.01 - $3,900.00 = $130.00
$3,900.01 - $4,050.00 = $135.00
$4,050.01 - $4,200.00 = $140.00
$4,200.01 - $4,350.00 = $145.00
$4,350.01 - $4,500.00 = $150.00
$4,500.01 - $4,650.00 = $155.00
$4,650.01 - $4,800.00 = $160.00
$4,800.01 - $4,950.00 = $165.00
$4,950.01 - $5,000.00 = $170.00

 

[SkipVought]

Assuming that you have 3 columns, get rid of column 2.

use MATCH with a argument 3 -1 or VLOOKUP with last argument TRUE I believe.

you may need to adjust the relationship between column 1 & NEW column 2.

Skip,

_[red][/red]

 

[turks393]

I got rid of the column 2 and now there are two comlums next to each other (column A is the low number and column B is hte high number. Column D has the number I want to come back.

I tried this:
=VLOOKUP(A13,'sheet1'!A5:B38,'sheet1'!D538,TRUE)
but it returns a #REF!

A13 has the number I will be entering (50 to 5000), A5 to A38 has the low number (50, 150.01, etc), B5 to B38 has the high number (150, 300, etc). D5 to D38 has the value I want returned.

 

[Gavona]

You have one too few parameters.
Vlookup(value to lookup, rangetolookin, column to get value from, True/False)

Suggestion:
use the function wizard.
use help


Gavin

 

[stubnski]

Hi,
could you use a regular lookup function?
=lookup(c1,a1:a10,b1:b10)

where c1 is the value your looking up, a1:a10 is your dollar values, and b1:b10 is the number you want to return.

Stubnski

 

[Gavona]

=VLOOKUP(A13,'sheet1'!A5:B38,'sheet1'!D538,2,TRUE)


Gavin