| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if value is between two numbers
I have the following ranges:
A B C D GENERAL 110 0.00 $0.21 GENERAL 125 0.22 $10.00 GENERAL 130 10.01 $50.00 GENERAL 140 50.01 $100.00 GENERAL 150 100.01 $500.00 GENERAL 160 500.01 GENERAL2 10 0.00 $0.21 GENERAL2 20 0.22 $10.00 GENERAL2 30 10.01 $50.00 GENERAL2 40 50.01 $100.00 GENERAL2 50 100.01 $500.00 GENERAL2 60 500.01 I want to search 3.98 GENERAL where the function would lookup 'GENERAL' in the stated ranges find which row the value of '3.98' is and return the value in column B. any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if value is between two numbers
Samuel,
I don't see a value of 3.98 Mike "samuel" wrote: I have the following ranges: A B C D GENERAL 110 0.00 $0.21 GENERAL 125 0.22 $10.00 GENERAL 130 10.01 $50.00 GENERAL 140 50.01 $100.00 GENERAL 150 100.01 $500.00 GENERAL 160 500.01 GENERAL2 10 0.00 $0.21 GENERAL2 20 0.22 $10.00 GENERAL2 30 10.01 $50.00 GENERAL2 40 50.01 $100.00 GENERAL2 50 100.01 $500.00 GENERAL2 60 500.01 I want to search 3.98 GENERAL where the function would lookup 'GENERAL' in the stated ranges find which row the value of '3.98' is and return the value in column B. any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if value is between two numbers
correct. 3.98 would be between 0.22 & 10.00
so the value needing to be returned is 125 "Mike H" wrote: Samuel, I don't see a value of 3.98 Mike "samuel" wrote: I have the following ranges: A B C D GENERAL 110 0.00 $0.21 GENERAL 125 0.22 $10.00 GENERAL 130 10.01 $50.00 GENERAL 140 50.01 $100.00 GENERAL 150 100.01 $500.00 GENERAL 160 500.01 GENERAL2 10 0.00 $0.21 GENERAL2 20 0.22 $10.00 GENERAL2 30 10.01 $50.00 GENERAL2 40 50.01 $100.00 GENERAL2 50 100.01 $500.00 GENERAL2 60 500.01 I want to search 3.98 GENERAL where the function would lookup 'GENERAL' in the stated ranges find which row the value of '3.98' is and return the value in column B. any ideas? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if value is between two numbers
Try this *array* formula,
Where your variables are assigned to particular cells. General in E1 3.98 in E2 =INDEX(B1:B12,MATCH(1,(E2=C1:C12)*(E2<=D1:D12)*(A 1:A12=E1),0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "samuel" wrote in message ... correct. 3.98 would be between 0.22 & 10.00 so the value needing to be returned is 125 "Mike H" wrote: Samuel, I don't see a value of 3.98 Mike "samuel" wrote: I have the following ranges: A B C D GENERAL 110 0.00 $0.21 GENERAL 125 0.22 $10.00 GENERAL 130 10.01 $50.00 GENERAL 140 50.01 $100.00 GENERAL 150 100.01 $500.00 GENERAL 160 500.01 GENERAL2 10 0.00 $0.21 GENERAL2 20 0.22 $10.00 GENERAL2 30 10.01 $50.00 GENERAL2 40 50.01 $100.00 GENERAL2 50 100.01 $500.00 GENERAL2 60 500.01 I want to search 3.98 GENERAL where the function would lookup 'GENERAL' in the stated ranges find which row the value of '3.98' is and return the value in column B. any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if value is between two numbers
Try this:
=INDEX(B1:B12,MAX(INDEX((A1:A12="General")*(C1:C12 <=3.98)*ROW(C1:C12),))) "samuel" wrote: I have the following ranges: A B C D GENERAL 110 0.00 $0.21 GENERAL 125 0.22 $10.00 GENERAL 130 10.01 $50.00 GENERAL 140 50.01 $100.00 GENERAL 150 100.01 $500.00 GENERAL 160 500.01 GENERAL2 10 0.00 $0.21 GENERAL2 20 0.22 $10.00 GENERAL2 30 10.01 $50.00 GENERAL2 40 50.01 $100.00 GENERAL2 50 100.01 $500.00 GENERAL2 60 500.01 I want to search 3.98 GENERAL where the function would lookup 'GENERAL' in the stated ranges find which row the value of '3.98' is and return the value in column B. any ideas? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if value is between two numbers
Yep, I missed that 500!<g
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Teethless mama" wrote in message ... Try this: =INDEX(B1:B12,MAX(INDEX((A1:A12="General")*(C1:C12 <=3.98)*ROW(C1:C12),))) "samuel" wrote: I have the following ranges: A B C D GENERAL 110 0.00 $0.21 GENERAL 125 0.22 $10.00 GENERAL 130 10.01 $50.00 GENERAL 140 50.01 $100.00 GENERAL 150 100.01 $500.00 GENERAL 160 500.01 GENERAL2 10 0.00 $0.21 GENERAL2 20 0.22 $10.00 GENERAL2 30 10.01 $50.00 GENERAL2 40 50.01 $100.00 GENERAL2 50 100.01 $500.00 GENERAL2 60 500.01 I want to search 3.98 GENERAL where the function would lookup 'GENERAL' in the stated ranges find which row the value of '3.98' is and return the value in column B. any ideas? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if value is between two numbers
Use TM's formula!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... Try this *array* formula, Where your variables are assigned to particular cells. General in E1 3.98 in E2 =INDEX(B1:B12,MATCH(1,(E2=C1:C12)*(E2<=D1:D12)*(A 1:A12=E1),0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "samuel" wrote in message ... correct. 3.98 would be between 0.22 & 10.00 so the value needing to be returned is 125 "Mike H" wrote: Samuel, I don't see a value of 3.98 Mike "samuel" wrote: I have the following ranges: A B C D GENERAL 110 0.00 $0.21 GENERAL 125 0.22 $10.00 GENERAL 130 10.01 $50.00 GENERAL 140 50.01 $100.00 GENERAL 150 100.01 $500.00 GENERAL 160 500.01 GENERAL2 10 0.00 $0.21 GENERAL2 20 0.22 $10.00 GENERAL2 30 10.01 $50.00 GENERAL2 40 50.01 $100.00 GENERAL2 50 100.01 $500.00 GENERAL2 60 500.01 I want to search 3.98 GENERAL where the function would lookup 'GENERAL' in the stated ranges find which row the value of '3.98' is and return the value in column B. any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Decimal Numbers typed into Excel 2003 read as whole numbers | Excel Discussion (Misc queries) | |||
| Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
| Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
| to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions | |||
| Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
Linear Mode
