Question about vlookup

**pepsi** · 03-02-2009, 11:44 AM

Quantity----------------Price---------------Cost
50 to 99 boxes--------£596.48--------------£32.54
100 to 149 boxes ------£65.01

Theres the data, and i want to create a vlookup, range, wherein another spreadsheet i can have Quantity 134, and it will display the price (65.01)

how do i go about doing this

thanks again

**shg** · 03-02-2009, 11:48 AM

Welcome to the forum.

Skip the "to xx", it's redundant. Just use a two-column table.

**NBVC** · 03-02-2009, 11:50 AM

In the Qty column place only the lower bounds, ie. 0, 50,100

Then use Lookup()

=Lookup(A1,Sheet1!$A$1:$B$100)

where A1 is your lookup value (134)

And Sheet1!A1:B100 is your table.. in ascending order (make sure to have a 0 as your first entry and what you want returned if lookup value 0 to 50

**pepsi** · 03-02-2009, 11:59 AM

hey soz, forgot to mention that i can't remove the 'xx to xx boxes' part, it needs to stay there.

this parts rally confusing me :O

**NBVC** · 03-02-2009, 12:04 PM

You would need something like:

=INDEX(Sheet1!$B$1:$B$100,MATCH(K1,LEFT(Sheet1!$A$1:$A$100,FIND(" ",Sheet1!$A$1:$A$100)-1)+0))

which is confirmed with CTRL+SHIFT+ENTER instead of just ENTER... you will see { } brackets appear around the formula.

**pepsi** · 03-02-2009, 12:14 PM

is there a way you could show me how to do that using the Vlookup function box instead of the formual, as i dont userstand that :S

**NBVC** · 03-02-2009, 12:24 PM

See attached...

In Sheet2, A1 enter a number, Sheet2, B1 gets a result from table in Sheet1, A:B

Adjust the ranges and confirm the formula by holding CTRL+SHIFT together and pressing ENTER.

Alternatively, if you really want to use Vlookup, then add a helper column between the current 2 columns that extract the lower bound.. with formula: =LEFT(A1,FIND(" ",A1)-1)+0 and then use that as your lookup range..