| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
This formula works, but of course it only gives the first instance:
=INDEX($A$2:$A$100,MATCH(1,($L$2:$L$100<"")*($L$2 :$L$100<"Resolved"),0)). So I tried created this formula as an array: =INDEX($A$2:$A$100, SMALL(AND(IF(($L$2:$L$100<""),( $L$2:$L$100<"Resolved")), ROW($A$2:$A$100)),ROW(1:1))) I receive #N/A. Any suggestions on how to fix the formula? Column A has the information I'd like returned if the conditions are met in column L. Column L is the result of a VLOOKUP you all helped me with previously. Eventually I'll evolve the formula to do the look up from another worksheet. Is this enough information or do you need more? Thank in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with INDEX lookup
Try it like this:
=INDEX($A$2:$A$100,SMALL(IF(($L$2:$L$100<"")*( $L$2:$L$100<"Resolved"),ROW($A$2:$A$100)),ROWS(A$ 1:A1))-ROW(A$2)+1) Don't forget: array entered! -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... This formula works, but of course it only gives the first instance: =INDEX($A$2:$A$100,MATCH(1,($L$2:$L$100<"")*($L$2 :$L$100<"Resolved"),0)). So I tried created this formula as an array: =INDEX($A$2:$A$100, SMALL(AND(IF(($L$2:$L$100<""),( $L$2:$L$100<"Resolved")), ROW($A$2:$A$100)),ROW(1:1))) I receive #N/A. Any suggestions on how to fix the formula? Column A has the information I'd like returned if the conditions are met in column L. Column L is the result of a VLOOKUP you all helped me with previously. Eventually I'll evolve the formula to do the look up from another worksheet. Is this enough information or do you need more? Thank in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with INDEX lookup
Thank you, but I still get #N/A. I copied the formula exactly. was I supposed
to make some customizations? "T. Valko" wrote: Try it like this: =INDEX($A$2:$A$100,SMALL(IF(($L$2:$L$100<"")*( $L$2:$L$100<"Resolved"),ROW($A$2:$A$100)),ROWS(A$ 1:A1))-ROW(A$2)+1) Don't forget: array entered! -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... This formula works, but of course it only gives the first instance: =INDEX($A$2:$A$100,MATCH(1,($L$2:$L$100<"")*($L$2 :$L$100<"Resolved"),0)). So I tried created this formula as an array: =INDEX($A$2:$A$100, SMALL(AND(IF(($L$2:$L$100<""),( $L$2:$L$100<"Resolved")), ROW($A$2:$A$100)),ROW(1:1))) I receive #N/A. Any suggestions on how to fix the formula? Column A has the information I'd like returned if the conditions are met in column L. Column L is the result of a VLOOKUP you all helped me with previously. Eventually I'll evolve the formula to do the look up from another worksheet. Is this enough information or do you need more? Thank in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with INDEX lookup
I still get #N/A.
Column L is the result of a VLOOKUP Are there any #N/A errors already in col L? If so, you need to fix them. -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Thank you, but I still get #N/A. I copied the formula exactly. was I supposed to make some customizations? "T. Valko" wrote: Try it like this: =INDEX($A$2:$A$100,SMALL(IF(($L$2:$L$100<"")*( $L$2:$L$100<"Resolved"),ROW($A$2:$A$100)),ROWS(A$ 1:A1))-ROW(A$2)+1) Don't forget: array entered! -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... This formula works, but of course it only gives the first instance: =INDEX($A$2:$A$100,MATCH(1,($L$2:$L$100<"")*($L$2 :$L$100<"Resolved"),0)). So I tried created this formula as an array: =INDEX($A$2:$A$100, SMALL(AND(IF(($L$2:$L$100<""),( $L$2:$L$100<"Resolved")), ROW($A$2:$A$100)),ROW(1:1))) I receive #N/A. Any suggestions on how to fix the formula? Column A has the information I'd like returned if the conditions are met in column L. Column L is the result of a VLOOKUP you all helped me with previously. Eventually I'll evolve the formula to do the look up from another worksheet. Is this enough information or do you need more? Thank in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with INDEX lookup
Col L uses the following formula:
=IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") From what I can tell, when the # being looked up on the next worksheet isn't there, it returns the #N/A error. Which was fine until the INDEX formula wouldn't work. When I tried putting ISNA in there, I received a blank - which I don't want. Any suggestions? "T. Valko" wrote: I still get #N/A. Column L is the result of a VLOOKUP Are there any #N/A errors already in col L? If so, you need to fix them. -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Thank you, but I still get #N/A. I copied the formula exactly. was I supposed to make some customizations? "T. Valko" wrote: Try it like this: =INDEX($A$2:$A$100,SMALL(IF(($L$2:$L$100<"")*( $L$2:$L$100<"Resolved"),ROW($A$2:$A$100)),ROWS(A$ 1:A1))-ROW(A$2)+1) Don't forget: array entered! -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... This formula works, but of course it only gives the first instance: =INDEX($A$2:$A$100,MATCH(1,($L$2:$L$100<"")*($L$2 :$L$100<"Resolved"),0)). So I tried created this formula as an array: =INDEX($A$2:$A$100, SMALL(AND(IF(($L$2:$L$100<""),( $L$2:$L$100<"Resolved")), ROW($A$2:$A$100)),ROW(1:1))) I receive #N/A. Any suggestions on how to fix the formula? Column A has the information I'd like returned if the conditions are met in column L. Column L is the result of a VLOOKUP you all helped me with previously. Eventually I'll evolve the formula to do the look up from another worksheet. Is this enough information or do you need more? Thank in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with INDEX lookup
What type of data does the VLOOKUP return? Is it text, numeric or could it
be either? What version of Excel are you using? -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Col L uses the following formula: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") From what I can tell, when the # being looked up on the next worksheet isn't there, it returns the #N/A error. Which was fine until the INDEX formula wouldn't work. When I tried putting ISNA in there, I received a blank - which I don't want. Any suggestions? "T. Valko" wrote: I still get #N/A. Column L is the result of a VLOOKUP Are there any #N/A errors already in col L? If so, you need to fix them. -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Thank you, but I still get #N/A. I copied the formula exactly. was I supposed to make some customizations? "T. Valko" wrote: Try it like this: =INDEX($A$2:$A$100,SMALL(IF(($L$2:$L$100<"")*( $L$2:$L$100<"Resolved"),ROW($A$2:$A$100)),ROWS(A$ 1:A1))-ROW(A$2)+1) Don't forget: array entered! -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... This formula works, but of course it only gives the first instance: =INDEX($A$2:$A$100,MATCH(1,($L$2:$L$100<"")*($L$2 :$L$100<"Resolved"),0)). So I tried created this formula as an array: =INDEX($A$2:$A$100, SMALL(AND(IF(($L$2:$L$100<""),( $L$2:$L$100<"Resolved")), ROW($A$2:$A$100)),ROW(1:1))) I receive #N/A. Any suggestions on how to fix the formula? Column A has the information I'd like returned if the conditions are met in column L. Column L is the result of a VLOOKUP you all helped me with previously. Eventually I'll evolve the formula to do the look up from another worksheet. Is this enough information or do you need more? Thank in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Which is better? LOOKUP or INDEX? | Excel Worksheet Functions | |||
| Lookup? Index? or something else? | Excel Discussion (Misc queries) | |||
| Either LOOKUP or INDEX Help | Excel Worksheet Functions | |||
| Lookup/match/index | Excel Discussion (Misc queries) | |||
| Lookup or Index/Match | Excel Discussion (Misc queries) | |||
![Vegas Sara[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
