How to use conditons within a LOOKUP function?

  • Thread starter Thread starter THIS-IS-A-JOURNEY
  • Start date Start date
T

THIS-IS-A-JOURNEY

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?
 
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
 
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
 
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
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

THIS-IS-A-JOURNEY said:
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 said:
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 said:
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

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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top