| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use conditons within a LOOKUP function?
I'm working with a function that needs to look at the number according to
whether its less than. =VLOOKUP(35600, A2:C3,2, [True or False]) A B C 1 wages fed tax state tax 2 < 20000 10% 3.0% 3 < 40000 15% 3.5% What I want to do is make it so that is would take 35600 look at it and see that it is less than 40000 and then use 15% By default it goes to the lowest number and comes up with 10%. How can I use conditioning? -- [Journey of the Way] |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use conditons within a LOOKUP function?
Hi,
While this problem can be solved with your current data layout, if you can change the data layout a bit (column A only), the formula becomes smaller and easier to understand. In A2, instead of <20000, type 40000 (without the < sign). In A3, instead of <40000, type 20000 (without the < sign). Now try this formula to get the fed tax. =INDEX($A$1:$C$3,MATCH(A6,$A$1:$A$3,-1),2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "THIS-IS-A-JOURNEY" wrote in message ... I'm working with a function that needs to look at the number according to whether its less than. =VLOOKUP(35600, A2:C3,2, [True or False]) A B C 1 wages fed tax state tax 2 < 20000 10% 3.0% 3 < 40000 15% 3.5% What I want to do is make it so that is would take 35600 look at it and see that it is less than 40000 and then use 15% By default it goes to the lowest number and comes up with 10%. How can I use conditioning? -- [Journey of the Way] |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use conditons within a LOOKUP function?
Hi,
Sorry but in the previous post, when you swap the A2:A3, you obviously also need to swap the values in the matrix, B2:C3 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "THIS-IS-A-JOURNEY" wrote in message ... I'm working with a function that needs to look at the number according to whether its less than. =VLOOKUP(35600, A2:C3,2, [True or False]) A B C 1 wages fed tax state tax 2 < 20000 10% 3.0% 3 < 40000 15% 3.5% What I want to do is make it so that is would take 35600 look at it and see that it is less than 40000 and then use 15% By default it goes to the lowest number and comes up with 10%. How can I use conditioning? -- [Journey of the Way] |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use conditons within a LOOKUP function?
Hi,
If you want the data layout to remain, you may try this array formula (Ctrl+Shift+Enter). Just ensure that <20000 is 20000 and <40000 is 40000. Cell A6 has 35600 =INDEX($A$1:$C$3,MATCH(MIN(IF((A2:A3-A6)0,A2:A3)),$A$1:$A$3,0),2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "THIS-IS-A-JOURNEY" wrote in message ... I'm working with a function that needs to look at the number according to whether its less than. =VLOOKUP(35600, A2:C3,2, [True or False]) A B C 1 wages fed tax state tax 2 < 20000 10% 3.0% 3 < 40000 15% 3.5% What I want to do is make it so that is would take 35600 look at it and see that it is less than 40000 and then use 15% By default it goes to the lowest number and comes up with 10%. How can I use conditioning? -- [Journey of the Way] |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use conditons within a LOOKUP function?
I tried both formulas you gave me, but they don't seem to work. The class
that i'm working on this formula for is an introductory...so it's shouldn't be too heavy. -- [Journey of the Way] "Ashish Mathur" wrote: Hi, If you want the data layout to remain, you may try this array formula (Ctrl+Shift+Enter). Just ensure that <20000 is 20000 and <40000 is 40000. Cell A6 has 35600 =INDEX($A$1:$C$3,MATCH(MIN(IF((A2:A3-A6)0,A2:A3)),$A$1:$A$3,0),2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "THIS-IS-A-JOURNEY" wrote in message ... I'm working with a function that needs to look at the number according to whether its less than. =VLOOKUP(35600, A2:C3,2, [True or False]) A B C 1 wages fed tax state tax 2 < 20000 10% 3.0% 3 < 40000 15% 3.5% What I want to do is make it so that is would take 35600 look at it and see that it is less than 40000 and then use 15% By default it goes to the lowest number and comes up with 10%. How can I use conditioning? -- [Journey of the Way] |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use conditons within a LOOKUP function?
Never mind. your good. thanks for the help. now I have to explain how it
works, but i got it. backwards-engineer. -- [Journey of the Way] "THIS-IS-A-JOURNEY" wrote: I tried both formulas you gave me, but they don't seem to work. The class that i'm working on this formula for is an introductory...so it's shouldn't be too heavy. -- [Journey of the Way] "Ashish Mathur" wrote: Hi, If you want the data layout to remain, you may try this array formula (Ctrl+Shift+Enter). Just ensure that <20000 is 20000 and <40000 is 40000. Cell A6 has 35600 =INDEX($A$1:$C$3,MATCH(MIN(IF((A2:A3-A6)0,A2:A3)),$A$1:$A$3,0),2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "THIS-IS-A-JOURNEY" wrote in message ... I'm working with a function that needs to look at the number according to whether its less than. =VLOOKUP(35600, A2:C3,2, [True or False]) A B C 1 wages fed tax state tax 2 < 20000 10% 3.0% 3 < 40000 15% 3.5% What I want to do is make it so that is would take 35600 look at it and see that it is less than 40000 and then use 15% By default it goes to the lowest number and comes up with 10%. How can I use conditioning? -- [Journey of the Way] |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use conditons within a LOOKUP function?
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "THIS-IS-A-JOURNEY" wrote in message ... Never mind. your good. thanks for the help. now I have to explain how it works, but i got it. backwards-engineer. -- [Journey of the Way] "THIS-IS-A-JOURNEY" wrote: I tried both formulas you gave me, but they don't seem to work. The class that i'm working on this formula for is an introductory...so it's shouldn't be too heavy. -- [Journey of the Way] "Ashish Mathur" wrote: Hi, If you want the data layout to remain, you may try this array formula (Ctrl+Shift+Enter). Just ensure that <20000 is 20000 and <40000 is 40000. Cell A6 has 35600 =INDEX($A$1:$C$3,MATCH(MIN(IF((A2:A3-A6)0,A2:A3)),$A$1:$A$3,0),2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "THIS-IS-A-JOURNEY" wrote in message ... I'm working with a function that needs to look at the number according to whether its less than. =VLOOKUP(35600, A2:C3,2, [True or False]) A B C 1 wages fed tax state tax 2 < 20000 10% 3.0% 3 < 40000 15% 3.5% What I want to do is make it so that is would take 35600 look at it and see that it is less than 40000 and then use 15% By default it goes to the lowest number and comes up with 10%. How can I use conditioning? -- [Journey of the Way] |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula with conditons | Excel Worksheet Functions | |||
| Formula with conditons | Excel Worksheet Functions | |||
| Formula with conditons | Excel Worksheet Functions | |||
| Increase capability for formatting beyond three conditons. | Excel Discussion (Misc queries) | |||
| How can I use SUMIF and AND to test for two conditons before summ. | Excel Worksheet Functions | |||
Hybrid Mode
