Value Ranges

[CBWallace]

Here is my issue, I want to redo an Excel spreadsheet to input a price value onto page A from a value range from Page B based on a gallon value from Page A.

Example:

Let's say the spreadsheet defines a need for 56 gallons of a product. I want the spreadsheet to take that value and run it through the range on Page B and find the price per gallon based on the number of gallons needed and input it back into a different cell on Page A.

Please let me know if I need to clarify this further. Any and all help is welcome.

[zbor]

Use VLOOKUP function.
For more about it check Excel Help.

[NBVC]

Try Vlookup

eg.

=Vlookup(A1,Sheet2!A:B,2,FALSE)

where A1 is the gallons to lookup up in column A of Sheet2 and return value from column B of Sheet2

If the gallons in the table are listed in numerical ascending order.. then this will be faster and easier

=Lookup(A1,Sheet2!A:B)

where values are as previous..

[CBWallace]

The problem is that the value for the amount of gallons needed could be in the millions; I dont want to have to make Page B with 2 million+ cells. I want it to do something like:

Range (In Gallons) Price Per Gallon
>55 $356
55-275 $245.45
276-1100 $218.18
1101-2200 $190.90
2201+ $163.63

[zbor]

Yes...

Book2.xls

Try to look somewhere how VLOOKUP works (you'll find it very usefull)...

For example: http://www.youtube.com/watch?v=GZX2iXfqALs

[NBVC]

You can still use:

=Lookup(A1,Sheet2!A:B)

Just put the lower bound values in column A... don't need the Upper Bounds

eg.
A B
0 356
55 245.45
276 218.18
1101 190.90
2210 163.63

[CBWallace]

Thanks! I'll give it a go and let you know.

[CBWallace]

It's working! Thanks guys! I guess the part I was unaware of was that I didn't have to define the upper bounds of the range.

[zbor]

Beware, when you don't look for exact matches numbers must be sorted in ascending order!