VLOOKUP function returning data from ranges rayteach 30.09.2006 04:09:03 I want to use a VLOOKUP function to find data in a table that includes ranges. Example: # of tables Cost per table 1 – 19 $50.00 20 – 39 $45.00 If I then type 26 in a cell, $45.00 would appear. I can then have a simple multiplication formula in the next cell to calculate total cost. -- ray

Re: VLOOKUP function returning data from ranges "Biff" <biffinpitt[ at ]comcast.net> 30.09.2006 04:47:04 Setup your table like this: List the lower boundary of each range in column B: ..............B...............C 1...........0...............0 2...........1..............50 3..........20.............45 A1 = 26 =VLOOKUP(A1,B1:C3,2) Or: =LOOKUP(A1,B1:B3,C1:C3) Biff "rayteach" <rayteach[ at ]discussions.microsoft.com> wrote in message news:41D4B76B-DDCA-4FB0-BDFB-FB4E37FC1A45[ at ]microsoft.com... [Quoted Text] >I want to use a VLOOKUP function to find data in a table that includes > ranges. Example: > > # of tables Cost per table > 1 - 19 $50.00 > 20 - 39 $45.00 > > If I then type 26 in a cell, $45.00 would appear. I can then have a simple > multiplication formula in the next cell to calculate total cost. > > -- > ray

Re: VLOOKUP function returning data from ranges "Ken Johnson" <KenCJohnson[ at ]gmail.com> 30.09.2006 04:49:00 rayteach wrote: [Quoted Text] > I want to use a VLOOKUP function to find data in a table that includes > ranges. Example: > > # of tables Cost per table > 1 - 19 $50.00 > 20 - 39 $45.00 > > If I then type 26 in a cell, $45.00 would appear. I can then have a simple > multiplication formula in the next cell to calculate total cost. > > -- > ray Hi Ray, If the leftmost column of the lookup table only showed the upper limit of the range (19, 39 etc) then you could use =INT(A1/20)*20 + 19 (where the value in A1 is the lookup value) to convert the lookup value to the appropriate range upper limit. You could include and hide a column of range upper limit values. Ken Johnson

Re: VLOOKUP function returning data from ranges rayteach 30.09.2006 21:45:02 Thank you Ken for your reply. I had used the reply by Biff as that worked. -- ray "Ken Johnson" wrote: [Quoted Text] > rayteach wrote: > > I want to use a VLOOKUP function to find data in a table that includes > > ranges. Example: > > > > # of tables Cost per table > > 1 - 19 $50.00 > > 20 - 39 $45.00 > > > > If I then type 26 in a cell, $45.00 would appear. I can then have a simple > > multiplication formula in the next cell to calculate total cost. > > > > -- > > ray > > Hi Ray, > > If the leftmost column of the lookup table only showed the upper limit > of the range (19, 39 etc) then you could use =INT(A1/20)*20 + 19 (where > the value in A1 is the lookup value) to convert the lookup value to the > appropriate range upper limit. You could include and hide a column of > range upper limit values. > > Ken Johnson > >

Re: VLOOKUP function returning data from ranges rayteach 30.09.2006 21:46:01 Thank you for your response. It worked! -- ray "Biff" wrote: [Quoted Text] > Setup your table like this: > > List the lower boundary of each range in column B: > > ..............B...............C > 1...........0...............0 > 2...........1..............50 > 3..........20.............45 > > A1 = 26 > > =VLOOKUP(A1,B1:C3,2) > > Or: > > =LOOKUP(A1,B1:B3,C1:C3) > > Biff > > "rayteach" <rayteach[ at ]discussions.microsoft.com> wrote in message > news:41D4B76B-DDCA-4FB0-BDFB-FB4E37FC1A45[ at ]microsoft.com... > >I want to use a VLOOKUP function to find data in a table that includes > > ranges. Example: > > > > # of tables Cost per table > > 1 - 19 $50.00 > > 20 - 39 $45.00 > > > > If I then type 26 in a cell, $45.00 would appear. I can then have a simple > > multiplication formula in the next cell to calculate total cost. > > > > -- > > ray > > >