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 -