Formula to look at a table with "0-34,999, 34,000-79,999, etc.

T

tabian

I need a formula that will look a table with 3 columns and 13 rows in which
the data is:
OE Metric SW List
0 34,999,999 $27,930
35,000,000 79,999,999 $45,486
80,000,000 99,999,999 $64,638
100,000,000 174,999,999 $79,800
175,000,000 234,999,999 $95,760
235,000,000 299,999,999 $112,518
300,000,000 374,999,999 $132,468
375,000,000 499,999,999 $165,186
500,000,000 599,999,999 $191,520
600,000,000 699,999,999 $217,854
700,000,000 899,999,999 $244,188
900,000,000 999,999,999 $296,856
1,000,000,000 9,999,999,999 $296,856

I have a value that I need to find where it is on this table and return the
information from column C. For instance, if the OE is 322,000, I need it to
return the value of $132,468.

thanks.
tina
 
R

Rick Rothstein \(MVP - VB\)

Your values are 1000 times greater than the 322,000 number you gave as an
example; I presume you meant 322,000,000. Anyway, assuming all those figures
are actually numbers formatted to appear as you listed them, this formula
should do what you want...

=SUMPRODUCT((D1>=A1:A13)*(D1<=B1:B13)*(C1:C13))

assuming your values are in A1:C13 and the value you are searching for is in
D1.

Rick
 
R

Ron Rosenfeld

I need a formula that will look a table with 3 columns and 13 rows in which
the data is:
OE Metric SW List
0 34,999,999 $27,930
35,000,000 79,999,999 $45,486
80,000,000 99,999,999 $64,638
100,000,000 174,999,999 $79,800
175,000,000 234,999,999 $95,760
235,000,000 299,999,999 $112,518
300,000,000 374,999,999 $132,468
375,000,000 499,999,999 $165,186
500,000,000 599,999,999 $191,520
600,000,000 699,999,999 $217,854
700,000,000 899,999,999 $244,188
900,000,000 999,999,999 $296,856
1,000,000,000 9,999,999,999 $296,856

I have a value that I need to find where it is on this table and return the
information from column C. For instance, if the OE is 322,000, I need it to
return the value of $132,468.

thanks.
tina
Click to expand...

A simple VLOOKUP will do that.

I assume your OE of 322,000 is really 322,000,000. If not, you will have to
multiply it by 1,000.

In any event:

=VLOOKUP(A1,tbl,3)

where tbl is the location of your data table and can be a NAME'd range.

--ron
 
T

tabian

I sort of left out some information. Important information.

If the OE is 322,000,000 (like you all correctly assumed), then I want to
look at the table, find where this amount is between column A and column B,
then return the column C answer. Can you enter a "If the value of X is Less
than or equal to Y, then return Z" ? If so, then I could remove the
figures in column A and just use the last two columns. Or can I say "If X
is equal to or between the amounts listed in columns A & B, then return C" ??
 
R

Rick Rothstein \(MVP - VB\)

Staying with my SUMPRODUCT approach... if you set your data up like this
(assumed to be in A1:B14)...

0 0
34,999,999 $27,930
79,999,999 $45,486
99,999,999 $64,638
174,999,999 $79,800
234,999,999 $95,760
299,999,999 $112,518
374,999,999 $132,468
499,999,999 $165,186
599,999,999 $191,520
699,999,999 $217,854
899,999,999 $244,188
999,999,999 $296,856
9,999,999,999 $296,856

then this formula will return what you want...

=SUMPRODUCT((D1>=A1:A13)*(D1<=A2:A14)*(B2:B14))

again, with the look up value in D1.

Rick
 
R

Ron Rosenfeld

I sort of left out some information. Important information.

If the OE is 322,000,000 (like you all correctly assumed), then I want to
look at the table, find where this amount is between column A and column B,
then return the column C answer. Can you enter a "If the value of X is Less
than or equal to Y, then return Z" ? If so, then I could remove the
figures in column A and just use the last two columns. Or can I say "If X
is equal to or between the amounts listed in columns A & B, then return C" ??
Click to expand...

This "clarification" is unclear.

Post the value that gave you an incorrect answer, and what your expected answer
would have been.
--ron
 
R

Ron Rosenfeld

I sort of left out some information. Important information.

If the OE is 322,000,000 (like you all correctly assumed), then I want to
look at the table, find where this amount is between column A and column B,
then return the column C answer. Can you enter a "If the value of X is Less
than or equal to Y, then return Z" ? If so, then I could remove the
figures in column A and just use the last two columns. Or can I say "If X
is equal to or between the amounts listed in columns A & B, then return C" ??
Click to expand...


One other question: What do you want to happen if OE is, for example
99,999,999.50? That is not defined in your table.

--ron
 
R

Ron Rosenfeld

I sort of left out some information. Important information.

If the OE is 322,000,000 (like you all correctly assumed), then I want to
look at the table, find where this amount is between column A and column B,
then return the column C answer. Can you enter a "If the value of X is Less
than or equal to Y, then return Z" ? If so, then I could remove the
figures in column A and just use the last two columns. Or can I say "If X
is equal to or between the amounts listed in columns A & B, then return C" ??
Click to expand...

An alternative to what I posted, which would enable you to remove a column,
would be to remove the middle column, and change the VLOOKUP formula to check
Column 2 instead of three:

=VLOOKUP(A1,tbl,2)
--ron
 
I

ilia

If you use MATCH() to find the correct row, you don't even need the
upper limit. Assume the data from your original question is in
A1:C13, and the OE is a defined name or range, I think this will work:

=INDEX(C1:C13, MATCH(OE,A1:A13,1))

You don't even need the second column.
 
Top