A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to use conditons within a LOOKUP function?



 
 
Thread Tools Display Modes
  #1  
Old February 10th, 2010, 11:42 PM posted to microsoft.public.excel.worksheet.functions
THIS-IS-A-JOURNEY
external usenet poster
 
Posts: 3
Default 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  
Old February 11th, 2010, 12:45 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default 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  
Old February 11th, 2010, 12:50 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default 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  
Old February 11th, 2010, 12:51 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default 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  
Old February 11th, 2010, 04:52 AM posted to microsoft.public.excel.worksheet.functions
THIS-IS-A-JOURNEY
external usenet poster
 
Posts: 3
Default 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  
Old February 11th, 2010, 05:12 AM posted to microsoft.public.excel.worksheet.functions
THIS-IS-A-JOURNEY
external usenet poster
 
Posts: 3
Default 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  
Old February 12th, 2010, 01:41 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:47 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.