Excel - find where a number falls in a start and end range
 

I have 2 sets of data -
Table A
name date Start # End# Price Qty
DDA 3/1/2007 100 150 $75.00 4
DDA 3/1/2007 151 200 $45.00 4
DDA 3/1/2007 201 250 $25.00 4
DDA 8/1/2007 251 300 $90.00 8
DDA 8/1/2007 301 350 $20.00 9
BBD 8/1/2007 351 400 $55.00 21
BBD 8/1/2007 401 450 $58.00 54
BBD 7/7/2008 451 500 $60.00 28
BBD 7/7/2008 500 550 $74.00 9

and
Table B
Name1 Date1 Number
DDA 3/1/2007 115
DDA 3/1/2007 121
DDA 8/1/2007 254
DDA 8/1/2007 345
DDA 3/1/2008 388
BBD 8/1/2007 229
BBD 7/7/2008 458
DDA 3/1/2007 298
BBD 7/7/2008 508

I am trying to write a lookup function that will tell me where example
#number 115 on table B falls in table A(what range based on start# and end#)
and copy corresponding price and qty. (the date and name should match). I
have tried index and match and it is not working.
--
mmk

Excel - find where a number falls in a start and end range
 

On Mon, 20 Oct 2008 13:14:01 -0700, Mkuria wrote:

I have 2 sets of data -
Table A
name date Start # End# Price Qty
DDA 3/1/2007 100 150 $75.00 4
DDA 3/1/2007 151 200 $45.00 4
DDA 3/1/2007 201 250 $25.00 4
DDA 8/1/2007 251 300 $90.00 8
DDA 8/1/2007 301 350 $20.00 9
BBD 8/1/2007 351 400 $55.00 21
BBD 8/1/2007 401 450 $58.00 54
BBD 7/7/2008 451 500 $60.00 28
BBD 7/7/2008 500 550 $74.00 9

and
Table B
Name1 Date1 Number
DDA 3/1/2007 115
DDA 3/1/2007 121
DDA 8/1/2007 254
DDA 8/1/2007 345
DDA 3/1/2008 388
BBD 8/1/2007 229
BBD 7/7/2008 458
DDA 3/1/2007 298
BBD 7/7/2008 508

I am trying to write a lookup function that will tell me where example
#number 115 on table B falls in table A(what range based on start# and end#)
and copy corresponding price and qty. (the date and name should match). I
have tried index and match and it is not working.


Assume Table A starts in A1, so is in A1:F10

Price: =VLOOKUP(Number,$C$1:$F$10,3)
Quantity: =VLOOKUP(Number,$C$1:$F$10,4)
--ron

Excel - find where a number falls in a start and end range
 

Thanks Ron, this works for the number but it does not take into account the
date and name columns.
--
mmk


"Mkuria" wrote:

I have 2 sets of data -
Table A
name date Start # End# Price Qty
DDA 3/1/2007 100 150 $75.00 4
DDA 3/1/2007 151 200 $45.00 4
DDA 3/1/2007 201 250 $25.00 4
DDA 8/1/2007 251 300 $90.00 8
DDA 8/1/2007 301 350 $20.00 9
BBD 8/1/2007 351 400 $55.00 21
BBD 8/1/2007 401 450 $58.00 54
BBD 7/7/2008 451 500 $60.00 28
BBD 7/7/2008 500 550 $74.00 9

and
Table B
Name1 Date1 Number
DDA 3/1/2007 115
DDA 3/1/2007 121
DDA 8/1/2007 254
DDA 8/1/2007 345
DDA 3/1/2008 388
BBD 8/1/2007 229
BBD 7/7/2008 458
DDA 3/1/2007 298
BBD 7/7/2008 508

I am trying to write a lookup function that will tell me where example
#number 115 on table B falls in table A(what range based on start# and end#)
and copy corresponding price and qty. (the date and name should match). I
have tried index and match and it is not working.
--
mmk

Excel - find where a number falls in a start and end range
 

On Tue, 21 Oct 2008 05:58:01 -0700, Mkuria wrote:

Thanks Ron, this works for the number but it does not take into account the
date and name columns.
--
mmk


Ah, yes.

In my first hasty look at your problem, it seemed irrelevant. But I see it is
not.

This should work. It assumes that a data line from table B will only find a
match in a single line of Table A.

name, date, Start, End, Price and Qty are named columns in Table A.

This will return a zero if there is no match

=SUMPRODUCT((name=A15)*(date=B15)*(Start<=C15)*(En d=C15)*Price)

The data from Table B is in A15:C23

If you have Excel 2007, you could use:

=SUMIFS(Price,name,A15,date,B15,Start,"<="&C15,End ,"="&C15)

To obtain the Quantity, merely substitute Qty for Price in either of the above
formulas.
--ron