here is what i want to do:

2 columns: A & B

col A: list of months (12): cell A1:jan/06, A2: feb/06, A3: mar/06, A4:
apr/06, A5:...... A12: dec/06
col B: list of sales that correspond to the month (only 4): B1:100,
B2:112, B3:105, B4:112

I want a formula that will tell me the latest month that the sales is
inputed (in this case B4) = apr/06

i've gotten the formula to find the last number of the set: which give
me the number of "112" (i put this formula in D1)

=INDEX(E17:E28,MATCH(9.99999999999999E+307,B1:B12))

or

=LOOKUP(9.99999999999999E+307,B1:B12)


but where i'm stuck at is using index/match, (i put this formula in E1)

=INDEX($A$1:$A$12,MATCH(D1,$B$1:$B$12,0))


and in the example above, it would always return to me (feb/06) as it
is the same sales figure as apr/06, but I want the formula to to tell
me apr/06!

i've also tried changing the match type to '1' and it did do anything.

i feel i'm so close but cn't figure it out!

(E-Mail Removed) wrote...
....
>col A: list of months (12): cell A1:jan/06, A2: feb/06, A3: mar/06, A4:
>apr/06, A5:...... A12: dec/06
>col B: list of sales that correspond to the month (only 4): B1:100,
>B2:112, B3:105, B4:112
>
>I want a formula that will tell me the latest month that the sales is
>inputed (in this case B4) = apr/06
....
>but where i'm stuck at is using index/match, (i put this formula in E1)
>
>=INDEX($A$1:$A$12,MATCH(D1,$B$1:$B$12,0))

So what's D1?

FWIW, given your setup above (something in each cell in A1:A12, numbers
in B1:B4, nothing in B5:B12), both the following formulas return the
value in cell A4.

=LOOKUP(1E+300,B1:B12,A1:A12)

=INDEX(A1:A12,MATCH(1E+300,B1:B12))

Harlan Grove wrote...
>(E-Mail Removed) wrote...
>...
>>col A: list of months (12): cell A1:jan/06, A2: feb/06, A3: mar/06, A4:
>>apr/06, A5:...... A12: dec/06
>>col B: list of sales that correspond to the month (only 4): B1:100,
>>B2:112, B3:105, B4:112
....
>>=INDEX($A$1:$A$12,MATCH(D1,$B$1:$B$12,0))
....

OK, I missed that D1 contains 112, presumably as a number rather than
text. If so, then your MATCH call will return 2 since cell B2 equals
112. You can't reliably use a sales figure in one column to look up
values in other columns because sales figures are likely to contain
duplicate values.

If you're trying to find the last value entered in B1:B12, stick with
MATCH(1E300,B1:B12).

i will give that a try again tomorrow and play w it somemore i guess..


On Nov 8, 5:29 pm, "Harlan Grove" <hrln...@aol.com> wrote:
> Harlan Grove wrote...
> >ppaau...@gmail.com wrote...
> >...
> >>col A: list of months (12): cell A1:jan/06, A2: feb/06, A3: mar/06, A4:
> >>apr/06, A5:...... A12: dec/06
> >>col B: list of sales that correspond to the month (only 4): B1:100,
> >>B2:112, B3:105, B4:112
> ...
> >>=INDEX($A$1:$A$12,MATCH(D1,$B$1:$B$12,0))...
>
> OK, I missed that D1 contains 112, presumably as a number rather than
> text. If so, then your MATCH call will return 2 since cell B2 equals
> 112. You can't reliably use a sales figure in one column to look up
> values in other columns because sales figures are likely to contain
> duplicate values.
>
> If you're trying to find the last value entered in B1:B12, stick with
> MATCH(1E300,B1:B12).