Index(Match) question

  • Thread starter Thread starter djbeard83
  • Start date Start date
D

djbeard83

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:Y2>0,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
 
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...
 
Try this formula - regular entry:

=INDEX($B$1:$Y$1,MATCH(LOOKUP(2,1/(B2:Y2>0),B2:Y2),B2:Y2,0))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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:Y2>0,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
 
OK, that works now. Thanks for your help.

Jim Thomlinson said:
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...
Click to expand...
 
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))
 
Back
Top