Home 
Search 
Today's Posts 
#1




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 2340]). 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




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 2340]). 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




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




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 reenteed 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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Effective method to paste array formula  Excel Worksheet Functions  
Match then lookup  Excel Worksheet Functions  
Formula Problem  interrupted by #VALUE! in other cells!?  Excel Worksheet Functions  
Array formula returning wrong results  Excel Discussion (Misc queries)  
Array  Excel Worksheet Functions 