| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index(Match) question
I'm using the Index and Match functions to return the month where inventory
levels for different parts run out based on changing forecasted usage. Based on other discussions in this forum I was able to use the formula below, and it works great, with one problem: my date ranges are January 2009 to December 2010. With my current formula, if the inventory level runs out before Dec. '09, the formula will always return December 2010 (example, if inventory value hits zero in Sept. 2009, the formula will return December 2010; if it hits zero April 2010, it will return April 2010). What am I missing? =INDEX($B$1:$Y$1,MATCH(TRUE,B2:Y20,1)) This is a sample of how my data is set up: Jan-09 Feb-09 Mar-09 Apr-09 Part A 1212 852 421 0 Part B 52 0 0 0 Part C 3524 1254 0 0 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index(Match) question
The issue you are running into has to do with using the 1 in your match. 1
Assumes that your data is in sorted order. This allows the function to divide the data set and determine if the desired value is in the first half or the last half of the data set (method of bisection makes searching very fast). Since it finds the zero value at one end of the set, it is done... With an exact match it can not divide the data set so it starts at the beginning and just keeps checking until the desired result is found. Try this function... =INDEX($B$1:$Y$1,MATCH(TRUE,B2:Y2=0,0)) Array formula once again... -- HTH... Jim Thomlinson "djbeard83" wrote: I'm using the Index and Match functions to return the month where inventory levels for different parts run out based on changing forecasted usage. Based on other discussions in this forum I was able to use the formula below, and it works great, with one problem: my date ranges are January 2009 to December 2010. With my current formula, if the inventory level runs out before Dec. '09, the formula will always return December 2010 (example, if inventory value hits zero in Sept. 2009, the formula will return December 2010; if it hits zero April 2010, it will return April 2010). What am I missing? =INDEX($B$1:$Y$1,MATCH(TRUE,B2:Y20,1)) This is a sample of how my data is set up: Jan-09 Feb-09 Mar-09 Apr-09 Part A 1212 852 421 0 Part B 52 0 0 0 Part C 3524 1254 0 0 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index(Match) question
Try this formula - regular entry:
=INDEX($B$1:$Y$1,MATCH(LOOKUP(2,1/(B2:Y20),B2:Y2),B2:Y2,0)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "djbeard83" wrote in message ... I'm using the Index and Match functions to return the month where inventory levels for different parts run out based on changing forecasted usage. Based on other discussions in this forum I was able to use the formula below, and it works great, with one problem: my date ranges are January 2009 to December 2010. With my current formula, if the inventory level runs out before Dec. '09, the formula will always return December 2010 (example, if inventory value hits zero in Sept. 2009, the formula will return December 2010; if it hits zero April 2010, it will return April 2010). What am I missing? =INDEX($B$1:$Y$1,MATCH(TRUE,B2:Y20,1)) This is a sample of how my data is set up: Jan-09 Feb-09 Mar-09 Apr-09 Part A 1212 852 421 0 Part B 52 0 0 0 Part C 3524 1254 0 0 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index(Match) question
OK, that works now. Thanks for your help.
"Jim Thomlinson" wrote: The issue you are running into has to do with using the 1 in your match. 1 Assumes that your data is in sorted order. This allows the function to divide the data set and determine if the desired value is in the first half or the last half of the data set (method of bisection makes searching very fast). Since it finds the zero value at one end of the set, it is done... With an exact match it can not divide the data set so it starts at the beginning and just keeps checking until the desired result is found. Try this function... =INDEX($B$1:$Y$1,MATCH(TRUE,B2:Y2=0,0)) Array formula once again... -- HTH... Jim Thomlinson "djbeard83" wrote: I'm using the Index and Match functions to return the month where inventory levels for different parts run out based on changing forecasted usage. Based on other discussions in this forum I was able to use the formula below, and it works great, with one problem: my date ranges are January 2009 to December 2010. With my current formula, if the inventory level runs out before Dec. '09, the formula will always return December 2010 (example, if inventory value hits zero in Sept. 2009, the formula will return December 2010; if it hits zero April 2010, it will return April 2010). What am I missing? =INDEX($B$1:$Y$1,MATCH(TRUE,B2:Y20,1)) This is a sample of how my data is set up: Jan-09 Feb-09 Mar-09 Apr-09 Part A 1212 852 421 0 Part B 52 0 0 0 Part C 3524 1254 0 0 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index(Match) question
My suggestion returned the last number in the datalist, the *last* month
with an inventory. If you're looking for the *first* 0, try this regular formula: =INDEX($B$1:$Y$1,MATCH(0,B2:Y2,0)) -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "djbeard83" wrote in message ... OK, that works now. Thanks for your help. "Jim Thomlinson" wrote: The issue you are running into has to do with using the 1 in your match. 1 Assumes that your data is in sorted order. This allows the function to divide the data set and determine if the desired value is in the first half or the last half of the data set (method of bisection makes searching very fast). Since it finds the zero value at one end of the set, it is done... With an exact match it can not divide the data set so it starts at the beginning and just keeps checking until the desired result is found. Try this function... =INDEX($B$1:$Y$1,MATCH(TRUE,B2:Y2=0,0)) Array formula once again... -- HTH... Jim Thomlinson "djbeard83" wrote: I'm using the Index and Match functions to return the month where inventory levels for different parts run out based on changing forecasted usage. Based on other discussions in this forum I was able to use the formula below, and it works great, with one problem: my date ranges are January 2009 to December 2010. With my current formula, if the inventory level runs out before Dec. '09, the formula will always return December 2010 (example, if inventory value hits zero in Sept. 2009, the formula will return December 2010; if it hits zero April 2010, it will return April 2010). What am I missing? =INDEX($B$1:$Y$1,MATCH(TRUE,B2:Y20,1)) This is a sample of how my data is set up: Jan-09 Feb-09 Mar-09 Apr-09 Part A 1212 852 421 0 Part B 52 0 0 0 Part C 3524 1254 0 0 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index / Match Question | Excel Worksheet Functions | |||
| Index Match question. | Excel Worksheet Functions | |||
| Index/Match question Need help! | Excel Worksheet Functions | |||
| Match or Index Question | Excel Worksheet Functions | |||
| Match + Index(?) Question | Excel Discussion (Misc queries) | |||
Linear Mode
