Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 9th 06, 03:43 AM posted to microsoft.public.excel.worksheet.functions
Leibtek
 
Posts: n/a
Default IF Formula to go through every line of array


Hi,

I have a worksheet with items numbered 1 through 5000, I have another
worksheet with the item categories. The categorie's worksheet has to
columns that indicate the range of items belonging to that category
(i.e. "Casual", Column D = 23 - Column E = 40 [Casual belongs to items
numbered 23-40]).

How do I get the category name in the item worksheet in its
corresponding items?

I've tried vlookup, if..etc. looks like I need a combination of both..
If somone can please help..

Thanks,
Leibtek


--
Leibtek
------------------------------------------------------------------------
Leibtek's Profile: http://www.excelforum.com/member.php...o&userid=32287
View this thread: http://www.excelforum.com/showthread...hreadid=520424


  #2   Report Post  
Old March 9th 06, 04:30 AM posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default IF Formula to go through every line of array

Hi!

(i.e. "Casual", Column D = 23 - Column E = 40


Assuming this is the layout:

......C...................D...............E
Casual................23..............40
Formal...............41..............58
Liesure...............59..............76

items numbered 1 through 5000


Are in the range A1:An

Enter this formula in B1 as an array using the key combo of
CTRL,SHIFT,ENTER:

=INDEX(C$1:C$3,MATCH(1,(A1=D$1$3)*(A1<=E$1:E$3) ,0))

Copy down as needed.

Add your sheet names and adjust the ranges to suit.

Biff

"Leibtek" wrote in
message ...

Hi,

I have a worksheet with items numbered 1 through 5000, I have another
worksheet with the item categories. The categorie's worksheet has to
columns that indicate the range of items belonging to that category
(i.e. "Casual", Column D = 23 - Column E = 40 [Casual belongs to items
numbered 23-40]).

How do I get the category name in the item worksheet in its
corresponding items?

I've tried vlookup, if..etc. looks like I need a combination of both..
If somone can please help..

Thanks,
Leibtek


--
Leibtek
------------------------------------------------------------------------
Leibtek's Profile:
http://www.excelforum.com/member.php...o&userid=32287
View this thread: http://www.excelforum.com/showthread...hreadid=520424



  #3   Report Post  
Old March 9th 06, 04:55 AM posted to microsoft.public.excel.worksheet.functions
Leibtek
 
Posts: n/a
Default IF Formula to go through every line of array


Enter this formula in B1 as an array using the key combo of

CTRL,SHIFT,ENTER:

I'm not sure how to do that...


Also, when you say "Match(1," what are referring to with the "1"?

Thanks,
Leibtek


--
Leibtek
------------------------------------------------------------------------
Leibtek's Profile: http://www.excelforum.com/member.php...o&userid=32287
View this thread: http://www.excelforum.com/showthread...hreadid=520424

  #4   Report Post  
Old March 9th 06, 06:06 AM posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default IF Formula to go through every line of array

Hi!

Array formulas are different.

Normally when you type a formula like: =SUM(A1:A10), you hit the ENTER key
to place the formula in a cell.

With array formulas there is a special sequence of keys that you MUST use.
Type the formula then hold down both the CTRL key and the SHIFT key then hit
ENTER. When done properly you'll see in the formula bar that the formula is
enclosed in squiggly braces { }. The braces denote an array formula. You
MUST use the key combination, you can't just type in the braces. Also, if
you edit an array formula it MUST be re-enteed as an array.

Also, when you say "Match(1," what are referring to with the "1"?


When these arrays are multiplied together:

(A1=D$1$3)*(A1<=E$1:E$3)

they will return either a 1 if the condtion(s) is true or a 0 if the
condition(s) is false. It would look something like this:

MATCH(1,{0;1;0}

Match 1 matches the 1 that's in the second position. This matching position
(2) is then passed to the Index function and the result of the formula is
the second value that is indexed in the Index function:

=INDEX(A1:A10

The value that would be returned is the value in A2 since it's in the second
position.

Biff

"Leibtek" wrote in
message ...

Enter this formula in B1 as an array using the key combo of

CTRL,SHIFT,ENTER:

I'm not sure how to do that...


Also, when you say "Match(1," what are referring to with the "1"?

Thanks,
Leibtek


--
Leibtek
------------------------------------------------------------------------
Leibtek's Profile:
http://www.excelforum.com/member.php...o&userid=32287
View this thread: http://www.excelforum.com/showthread...hreadid=520424



  #5   Report Post  
Old March 9th 06, 02:25 PM posted to microsoft.public.excel.worksheet.functions
Leibtek
 
Posts: n/a
Default IF Formula to go through every line of array


Thanks SOOO Much,

I really appreciate your help!

Leibtek


--
Leibtek
------------------------------------------------------------------------
Leibtek's Profile: http://www.excelforum.com/member.php...o&userid=32287
View this thread: http://www.excelforum.com/showthread...hreadid=520424



  #6   Report Post  
Old March 9th 06, 06:42 PM posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default IF Formula to go through every line of array

You're welcome!

Biff

"Leibtek" wrote in
message ...

Thanks SOOO Much,

I really appreciate your help!

Leibtek


--
Leibtek
------------------------------------------------------------------------
Leibtek's Profile:
http://www.excelforum.com/member.php...o&userid=32287
View this thread: http://www.excelforum.com/showthread...hreadid=520424





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Effective method to paste array formula kuansheng Excel Worksheet Functions 2 February 2nd 06 12:47 AM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 05:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 05:18 PM
Array formula returning wrong results TUNGANA KURMA RAJU Excel Discussion (Misc queries) 1 November 19th 05 10:29 AM
Array Brad Excel Worksheet Functions 9 October 17th 05 09:00 PM


All times are GMT +1. The time now is 06:53 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017