Biff
Thanks for the help. The more I think about it, the less I think LOOKUP
is the right way to go. The percentage I am looking up is 69%, slightly less
than the 69.5% in row 6, so I knew it would go back to 5, I was surprised to
see that the data sometimes does not change from one month to another and can
even have a negative payment (refund of an earlier overpayment).

I had ruled out VLOOKUP because VLOOKUP requires the lookup column to be the
leftmost column. Others I looked at like MATCH seem to require an exact
match, not feasible when the pivot table can go from any percent to a much
higher or even lower in a single month, or stay unchanged.

I am hoping you or somebody knows of another function (or formula, or VB)
that will find the closest lower percentage for my starting month and let me
proceed from that point on. Thanks again.

"T. Valko" wrote:

I read that lookup will find "the largest value that is less
than or equal to the lookup value",

*If* the lookup array is sorted ascending which yours isn't.

in this case month 5 with 35%.

Why isn't the correct result month 7 with 64.0% ?


--
Biff
Microsoft Excel MVP


"tmarsh7407" wrote in message
...
I read that lookup will find "the largest value that is less than or equal
to
the lookup value", in this case month 5 with 35%. I then have the
percent
catch up and use the table percentages for remaining forecast periods. If
you know of some other function (or even some short VB code, emphasis on
short) I would gladly consider it

"T. Valko" wrote:

I am looking for a month with 69%

If there is not an exact match (as there is not in your sample) what
result
do you expect?

--
Biff
Microsoft Excel MVP


"tmarsh7407" wrote in message
...
I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but
have a
problem that perhaps somebody can help with. I have a pivot table
built
with
three columns as in the following partial example (whole table is much
longer
but this should show the issue):

A B C
Month PayPercent CumPercent
1 24.1% 24.1%
2 0.0% 24.1%
3 10.9% 35.0%
4 0.0% 35.0%
5 0.0% 35.0%
6 34.5% 69.5%
7 -5.5% 64.0%

I want to use CumPercent to find a starting point for projects that
are
in
progress. I tried to use a vector form
LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84)
to look for the nearest percent in Column C and then return the
matching
Month number from column A. Unfortunately it seems to give up and
return
2
when I am looking for a month with 69% - I think it does not like
seeing
the
same value twice and decides that it will not go any further. Even
worse,
the negative percent for month 7 means the column is not always in
ascending
order. Any ideas?