If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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] |
Thread Tools | |
Display Modes | |
|
|