|
Formula to look at a table with "0-34,999, 34,000-79,999, etc.
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 |
Formula to look at a table with "0-34,999, 34,000-79,999, etc.
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 |
Formula to look at a table with "0-34,999, 34,000-79,999, etc.
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 |
Formula to look at a table with "0-34,999, 34,000-79,999, etc.
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 |
Formula to look at a table with "0-34,999, 34,000-79,999, etc.
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 |
Formula to look at a table with "0-34,999, 34,000-79,999, etc.
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 |
Formula to look at a table with "0-34,999, 34,000-79,999, etc.
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 |
Formula to look at a table with "0-34,999, 34,000-79,999, etc.
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 |
Formula to look at a table with "0-34,999, 34,000-79,999, etc.
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 - |
| All times are GMT +1. The time now is 01:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com