Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if cell falls within range of number (i.e. 2500 to 4999) | Excel Worksheet Functions | |||
Test if a date falls within a given range of dates in Excel? | Excel Worksheet Functions | |||
Calculate how much time falls between set start and stop times | Excel Worksheet Functions | |||
How to check if a date falls within a range(2 cells: Start/End Dat | Excel Worksheet Functions | |||
Formula to determine whether number falls within range?? | Excel Worksheet Functions |