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

[Mkuria]

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.

 

[Ron Rosenfeld]

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

 

[Mkuria]

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

 

[Ron Rosenfeld]

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

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)*(End>=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