Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index Formula Help
Excel2003 ... I have the following Formula which is working fine:
=INDEX('Short Desc'!$E$2:$E$10000,MATCH(LEFT($C3,SEARCH(" ",$C3)-1),'Short Desc'!$D$2:$D$10000,0))&" "&E3 Issue is ... Formula is returning 1st occurance of the Match found in the Index ... So I need to check a 2nd criteria ("WS1 Col B" vs "Short Desc Col B"). How do I add this 2nd criteria? ... Thanks ... Kha |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index Formula Help
Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" The below is just an example to show how to add more conditions. =INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10=D2)*($B$2:$B $10=D3),0)) the below are the two conditions $A$2:$A$10=D2 $B$2:$B$10=D3 If this post helps click Yes --------------- Jacob Skaria "Ken" wrote: Excel2003 ... I have the following Formula which is working fine: =INDEX('Short Desc'!$E$2:$E$10000,MATCH(LEFT($C3,SEARCH(" ",$C3)-1),'Short Desc'!$D$2:$D$10000,0))&" "&E3 Issue is ... Formula is returning 1st occurance of the Match found in the Index ... So I need to check a 2nd criteria ("WS1 Col B" vs "Short Desc Col B"). How do I add this 2nd criteria? ... Thanks ... Kha |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index Formula Help
Yes ... I have used similar formula before ... However, I am having a problem
writing into my existing formula ... Can you provide guidance ... Thanks ... Kha "Jacob Skaria" wrote: Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" The below is just an example to show how to add more conditions. =INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10=D2)*($B$2:$B $10=D3),0)) the below are the two conditions $A$2:$A$10=D2 $B$2:$B$10=D3 If this post helps click Yes --------------- Jacob Skaria "Ken" wrote: Excel2003 ... I have the following Formula which is working fine: =INDEX('Short Desc'!$E$2:$E$10000,MATCH(LEFT($C3,SEARCH(" ",$C3)-1),'Short Desc'!$D$2:$D$10000,0))&" "&E3 Issue is ... Formula is returning 1st occurance of the Match found in the Index ... So I need to check a 2nd criteria ("WS1 Col B" vs "Short Desc Col B"). How do I add this 2nd criteria? ... Thanks ... Kha |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index Formula Help
OK ... I got it ... :)
{=INDEX('Short Desc'!$E$2:$E$10000,MATCH(1,($B3='Short Desc'!$B$2:$B$10000)*(LEFT($C3,SEARCH(" ",$C3)-1)='Short Desc'!$D$2:$D$10000),0))&" "&$E3} Thanks ... Kha "Jacob Skaria" wrote: Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" The below is just an example to show how to add more conditions. =INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10=D2)*($B$2:$B $10=D3),0)) the below are the two conditions $A$2:$A$10=D2 $B$2:$B$10=D3 If this post helps click Yes --------------- Jacob Skaria "Ken" wrote: Excel2003 ... I have the following Formula which is working fine: =INDEX('Short Desc'!$E$2:$E$10000,MATCH(LEFT($C3,SEARCH(" ",$C3)-1),'Short Desc'!$D$2:$D$10000,0))&" "&E3 Issue is ... Formula is returning 1st occurance of the Match found in the Index ... So I need to check a 2nd criteria ("WS1 Col B" vs "Short Desc Col B"). How do I add this 2nd criteria? ... Thanks ... Kha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index Formula | Excel Worksheet Functions | |||
Formula Help (IF) ... INDEX | Excel Worksheet Functions | |||
If or index formula help | Excel Worksheet Functions | |||
Index formula help | Excel Discussion (Misc queries) | |||
Sum and Index formula? | Excel Discussion (Misc queries) |