| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect function in Index/Match Array
I have the following formula working correctly, but I also need to find the
next instances of the occurance and I'm not sure where the INDIRECT part of the formula should go. I couldn't tell from the other posts similar to this what I need to do in my particular situation. Can anyone help? Thanks! =INDEX('Master Com Plan'!G15:G44,MATCH(1,('Master Com Plan'!A15:A44="SDS PM")*('Master Com Plan'!H15:H44="SDS Leads")*('Master Com Plan'!Q15:Q44=7),0)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function in Index/Match Array
Try this array formula
=IF(ISERROR(SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))),"", INDEX($G$15:$G$44,SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))-ROW($G$15)+1)) -- __________________________________ HTH Bob "hoosier41" wrote in message ... I have the following formula working correctly, but I also need to find the next instances of the occurance and I'm not sure where the INDIRECT part of the formula should go. I couldn't tell from the other posts similar to this what I need to do in my particular situation. Can anyone help? Thanks! =INDEX('Master Com Plan'!G15:G44,MATCH(1,('Master Com Plan'!A15:A44="SDS PM")*('Master Com Plan'!H15:H44="SDS Leads")*('Master Com Plan'!Q15:Q44=7),0)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function in Index/Match Array
Thanks Bob, I think that starts me on the right path, but it's not returning
any value. I'm not getting an error or anything, just a blank cell. I'm wondering about the row references to $A1. My A1 has the title of the worksheet, but no values. Do I need something different here for the formula to work? "Bob Phillips" wrote: Try this array formula =IF(ISERROR(SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))),"", INDEX($G$15:$G$44,SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))-ROW($G$15)+1)) -- __________________________________ HTH Bob "hoosier41" wrote in message ... I have the following formula working correctly, but I also need to find the next instances of the occurance and I'm not sure where the INDIRECT part of the formula should go. I couldn't tell from the other posts similar to this what I need to do in my particular situation. Can anyone help? Thanks! =INDEX('Master Com Plan'!G15:G44,MATCH(1,('Master Com Plan'!A15:A44="SDS PM")*('Master Com Plan'!H15:H44="SDS Leads")*('Master Com Plan'!Q15:Q44=7),0)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function in Index/Match Array
The A1 is just a row number, it is referring explicitly to the cell data.
Did you array enter it? -- __________________________________ HTH Bob "hoosier41" wrote in message ... Thanks Bob, I think that starts me on the right path, but it's not returning any value. I'm not getting an error or anything, just a blank cell. I'm wondering about the row references to $A1. My A1 has the title of the worksheet, but no values. Do I need something different here for the formula to work? "Bob Phillips" wrote: Try this array formula =IF(ISERROR(SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))),"", INDEX($G$15:$G$44,SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))-ROW($G$15)+1)) -- __________________________________ HTH Bob "hoosier41" wrote in message ... I have the following formula working correctly, but I also need to find the next instances of the occurance and I'm not sure where the INDIRECT part of the formula should go. I couldn't tell from the other posts similar to this what I need to do in my particular situation. Can anyone help? Thanks! =INDEX('Master Com Plan'!G15:G44,MATCH(1,('Master Com Plan'!A15:A44="SDS PM")*('Master Com Plan'!H15:H44="SDS Leads")*('Master Com Plan'!Q15:Q44=7),0)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function in Index/Match Array
Yes, I entered it as an array and I should have two more instances that match
the criteria. It's not giving me an error like you get if you don't enter the array. I'm checking this criteria and pulling it from another worksheet in the same workbook, do I need to reference that sheet name instead of just the cell number, like $A$15? Thanks for all your help! "Bob Phillips" wrote: The A1 is just a row number, it is referring explicitly to the cell data. Did you array enter it? -- __________________________________ HTH Bob "hoosier41" wrote in message ... Thanks Bob, I think that starts me on the right path, but it's not returning any value. I'm not getting an error or anything, just a blank cell. I'm wondering about the row references to $A1. My A1 has the title of the worksheet, but no values. Do I need something different here for the formula to work? "Bob Phillips" wrote: Try this array formula =IF(ISERROR(SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))),"", INDEX($G$15:$G$44,SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))-ROW($G$15)+1)) -- __________________________________ HTH Bob "hoosier41" wrote in message ... I have the following formula working correctly, but I also need to find the next instances of the occurance and I'm not sure where the INDIRECT part of the formula should go. I couldn't tell from the other posts similar to this what I need to do in my particular situation. Can anyone help? Thanks! =INDEX('Master Com Plan'!G15:G44,MATCH(1,('Master Com Plan'!A15:A44="SDS PM")*('Master Com Plan'!H15:H44="SDS Leads")*('Master Com Plan'!Q15:Q44=7),0)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function in Index/Match Array
You would need to use the sheet name wherever applicable yes. Either the
INDEX, or the conditions. -- __________________________________ HTH Bob "hoosier41" wrote in message ... Yes, I entered it as an array and I should have two more instances that match the criteria. It's not giving me an error like you get if you don't enter the array. I'm checking this criteria and pulling it from another worksheet in the same workbook, do I need to reference that sheet name instead of just the cell number, like $A$15? Thanks for all your help! "Bob Phillips" wrote: The A1 is just a row number, it is referring explicitly to the cell data. Did you array enter it? -- __________________________________ HTH Bob "hoosier41" wrote in message ... Thanks Bob, I think that starts me on the right path, but it's not returning any value. I'm not getting an error or anything, just a blank cell. I'm wondering about the row references to $A1. My A1 has the title of the worksheet, but no values. Do I need something different here for the formula to work? "Bob Phillips" wrote: Try this array formula =IF(ISERROR(SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))),"", INDEX($G$15:$G$44,SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($ A1))-ROW($G$15)+1)) -- __________________________________ HTH Bob "hoosier41" wrote in message ... I have the following formula working correctly, but I also need to find the next instances of the occurance and I'm not sure where the INDIRECT part of the formula should go. I couldn't tell from the other posts similar to this what I need to do in my particular situation. Can anyone help? Thanks! =INDEX('Master Com Plan'!G15:G44,MATCH(1,('Master Com Plan'!A15:A44="SDS PM")*('Master Com Plan'!H15:H44="SDS Leads")*('Master Com Plan'!Q15:Q44=7),0)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match, Index, Indirect ? | Excel Worksheet Functions | |||
| Array formula with index + match | Excel Worksheet Functions | |||
| Help with an Index Match Array | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Index,Indirect, and Match | Excel Worksheet Functions | |||
Linear Mode
