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

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


"tabian" wrote in message
...
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

On Sat, 8 Sep 2007 09:42:05 -0700, tabian
wrote:

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


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

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" ??

"Ron Rosenfeld" wrote:

On Sat, 8 Sep 2007 09:42:05 -0700, tabian
wrote:

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


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

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


"tabian" wrote in message
...
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"
??

"Ron Rosenfeld" wrote:

On Sat, 8 Sep 2007 09:42:05 -0700, tabian

wrote:

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


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

On Sat, 8 Sep 2007 14:54:01 -0700, tabian
wrote:

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" ??

This "clarification" is unclear.

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

On Sat, 8 Sep 2007 14:54:01 -0700, tabian
wrote:

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" ??

"Ron Rosenfeld" wrote:

On Sat, 8 Sep 2007 09:42:05 -0700, tabian
wrote:

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


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



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

On Sat, 8 Sep 2007 14:54:01 -0700, tabian
wrote:

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" ??

"Ron Rosenfeld" wrote:

On Sat, 8 Sep 2007 09:42:05 -0700, tabian
wrote:

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


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


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

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.


On Sep 8, 5:54 pm, tabian wrote:
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" ??



"Ron Rosenfeld" wrote:
On Sat, 8 Sep 2007 09:42:05 -0700, tabian
wrote:

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

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- Hide quoted text -

- Show quoted text -