Simple (I hope) Look Up Question

[SteveD]

I have the following Lookup formula:

=LOOKUP(0.9,M19:CT19,M17:CT17)
Where
Row 17 is a cumulative counter
Row 19 is the cumulative count divided by the total count quantity.

I am looking for the value of the cumulative counter when the percent is equal to or OVER 90%, (so I used .9 in the lookup)

Row 19 may not include 90% exactly, depending on how the count is distributed from M to CT.

The formula above works well, except that it seems to give me the value right before the 90% where as I'm looking for the first value over 90%.
Can anyone help me out on this?

Thanks,
SteveD

[Rocky E]

You could look at VLOOKUP or HLOOKUP. The have a optional parameter called "Range_Lookup" that permits matching or the next higher value.

Just a thought. Hope this helps.

Rocky...

[Yogi Anand]

Hi SteveD:
The correct syntax of the VLOOKUP formula is:

=vlookup(lookup_value,table_array,col_index_num,range_lookup)

The third argument col_index_num is the column number in your lookup table that houses the field of values of interest.

The fourth argument range_lookup needs a value of TRUE or False. FALSE will match only exact values, whereas TRUE will match non-exact value equal to or smaleer than the lookup value.

Hope This Helps!

[SteveD]

Thanks!

The help says that the TRUE in HLOOKUP brings back the closest match while False brings back only an exact match. TRUE then may not bring back the next one over 90% if it is not also the closest... right?

Also, this data is set up in rows (as opposed to columns). Should I be using HLOOKUP or VLOOKUP? My first guess was HLOOKUP...

Thanks,
Steve

[SteveD]

Thanks!

The help says that the TRUE in HLOOKUP brings back the closest match while False brings back only an exact match. TRUE then may not bring back the next one over 90% if it is not also the closest... right?

Also, this data is set up in rows (as opposed to columns). Should I be using HLOOKUP or VLOOKUP? My first guess was HLOOKUP...

Thanks,
Steve

[Aladin Akyurek]

On 2002-04-30 13:13, SteveD wrote:
Thanks!

The help says that the TRUE in HLOOKUP brings back the closest match while False brings back only an exact match. TRUE then may not bring back the next one over 90% if it is not also the closest... right?

Also, this data is set up in rows (as opposed to columns). Should I be using HLOOKUP or VLOOKUP? My first guess was HLOOKUP...

Thanks,
Steve

Could you perhaps post a sample?

[SteveD]

Thanks for your help Aladin,

I'm still working with the rows I put in the first post. I tried to use HLOOKUP on that data and can't seem to get it to work correctly. Did I misunderstand what you are asking?

SteveD

[Aladin Akyurek]

On 2002-04-30 13:56, SteveD wrote:
Thanks for your help Aladin,

I'm still working with the rows I put in the first post. I tried to use HLOOKUP on that data and can't seem to get it to work correctly. Did I misunderstand what you are asking?

SteveD

I wanted to see just those 2 rows of data (cumulative counts and percentages) if possible.

BTW, if .90% is available in the % row, must the corresponding number in the cum count range retrieved or one to the right?

Aladin

[SteveD]

Aladin,

There are 80+ columns of data (Col M to Col CT). Here are the important parts from this example:

Row 17 ... 116 122 132 ...
Row 19 ... 80% 84% 91% ...

Row 17 is a complex function that sums the data in the first 15 rows and adds that sum to the cell before it. It is a running total of sorts.
Row 18 is the total number of units, the final number in the row 17 series.
Row 19 is the ratio between the two rows (the percent of counted units so far to the total number of units).

The lookup in the first post is returning the 122 number, but I'm looking for the 132 number because it breaks the 90% threshold.

Guess this is not as simple as I hoped.

Thanks so much for your help!

SteveD

[Aladin Akyurek]

On 2002-04-30 14:44, SteveD wrote:
Aladin,

There are 80+ columns of data (Col M to Col CT). Here are the important parts from this example:

Row 17 ... 116 122 132 ...
Row 19 ... 80% 84% 91% ...

Row 17 is a complex function that sums the data in the first 15 rows and adds that sum to the cell before it. It is a running total of sorts.
Row 18 is the total number of units, the final number in the row 17 series.
Row 19 is the ratio between the two rows (the percent of counted units so far to the total number of units).

The lookup in the first post is returning the 122 number, but I'm looking for the 132 number because it breaks the 90% threshold.

Guess this is not as simple as I hoped.

Thanks so much for your help!

SteveD

Try:

=OFFSET(M17,0,MATCH(0.9,M19:CT19),1,1)

Guess what? One question you didn't answer. I bet you'll come back with that.

Aladin