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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
I'm not sure how to tell if column A is brought in as text or number, is
there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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. |
#8
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),"") Try changing that formula to: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY",ISNUMBER(MATCH($A4,'25'!$A$2:$A$100,0))) ,VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I'm not sure how to tell if column A is brought in as text or number, is there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
I appreciate you helping me with this. The problem is, that when there is no
match in the look up it gives me a blank cell. The error is actuall preferable as it lets me know there's no match since thats part of what we're tracking. "T. Valko" wrote: Col L uses the following formula: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") Try changing that formula to: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY",ISNUMBER(MATCH($A4,'25'!$A$2:$A$100,0))) ,VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I'm not sure how to tell if column A is brought in as text or number, is there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
We'll get this sorted out sooner or later! <g
The "no match" of the lookup is a separate condition from $G4<"Resolved" and $C4="SAP-SECURITY". You can do this for the "no match": =IF(ISNA(MATCH($A4,'25'!$A$2:$A$100,0)),"No Match",......... The rest of the formula would be: ...IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),*what should go here*)) *what should go here* = what result do you want if/when $G4<"Resolved" and $C4="SAP-SECURITY" if FALSE? -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I appreciate you helping me with this. The problem is, that when there is no match in the look up it gives me a blank cell. The error is actuall preferable as it lets me know there's no match since thats part of what we're tracking. "T. Valko" wrote: Col L uses the following formula: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") Try changing that formula to: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY",ISNUMBER(MATCH($A4,'25'!$A$2:$A$100,0))) ,VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I'm not sure how to tell if column A is brought in as text or number, is there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
Sorry, I'm a bit confused. Column L
uses:=IF(AND($G2<"Resolved",$C2="SAP-SECURITY"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"") Current results include "In Progress", "Resolved", "On Hold", blank or the issue #N/A. Another column has (entered 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))) However, the #N/A causes the whole thing to return as #NA. When I entered:=IF(ISNA(MATCH($A2,'25'!$A$2:$A$100,0)),"N o Match",IF(AND($G2<"Resolved",$C2="SAP-SVC DESK"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"")) I received way too many "No Match" values. Do I change my Index formula to contain the three variables? ($G2<"Resolved",$C2="SAP-SECURITY",$N2="No Match") Or is there an easier way I'm missing because I really don't speak Excel very well? :) "T. Valko" wrote: We'll get this sorted out sooner or later! <g The "no match" of the lookup is a separate condition from $G4<"Resolved" and $C4="SAP-SECURITY". You can do this for the "no match": =IF(ISNA(MATCH($A4,'25'!$A$2:$A$100,0)),"No Match",......... The rest of the formula would be: ...IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),*what should go here*)) *what should go here* = what result do you want if/when $G4<"Resolved" and $C4="SAP-SECURITY" if FALSE? -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I appreciate you helping me with this. The problem is, that when there is no match in the look up it gives me a blank cell. The error is actuall preferable as it lets me know there's no match since thats part of what we're tracking. "T. Valko" wrote: Col L uses the following formula: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") Try changing that formula to: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY",ISNUMBER(MATCH($A4,'25'!$A$2:$A$100,0))) ,VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I'm not sure how to tell if column A is brought in as text or number, is there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
Ok, this should work...
Array entered** : =INDEX($A$2:$A$100,SMALL(IF(ISTEXT($L$2:$L$100),IF (($L$2:$L$100<"")*($L$2:$L$100<"Resolved"),ROW(A $2:A$100))),ROWS(A$1:A1))-ROW(A$2)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Sorry, I'm a bit confused. Column L uses:=IF(AND($G2<"Resolved",$C2="SAP-SECURITY"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"") Current results include "In Progress", "Resolved", "On Hold", blank or the issue #N/A. Another column has (entered 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))) However, the #N/A causes the whole thing to return as #NA. When I entered:=IF(ISNA(MATCH($A2,'25'!$A$2:$A$100,0)),"N o Match",IF(AND($G2<"Resolved",$C2="SAP-SVC DESK"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"")) I received way too many "No Match" values. Do I change my Index formula to contain the three variables? ($G2<"Resolved",$C2="SAP-SECURITY",$N2="No Match") Or is there an easier way I'm missing because I really don't speak Excel very well? :) "T. Valko" wrote: We'll get this sorted out sooner or later! <g The "no match" of the lookup is a separate condition from $G4<"Resolved" and $C4="SAP-SECURITY". You can do this for the "no match": =IF(ISNA(MATCH($A4,'25'!$A$2:$A$100,0)),"No Match",......... The rest of the formula would be: ...IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),*what should go here*)) *what should go here* = what result do you want if/when $G4<"Resolved" and $C4="SAP-SECURITY" if FALSE? -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I appreciate you helping me with this. The problem is, that when there is no match in the look up it gives me a blank cell. The error is actuall preferable as it lets me know there's no match since thats part of what we're tracking. "T. Valko" wrote: Col L uses the following formula: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") Try changing that formula to: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY",ISNUMBER(MATCH($A4,'25'!$A$2:$A$100,0))) ,VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I'm not sure how to tell if column A is brought in as text or number, is there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
Almost perfect. 2 more questions: Is there a way to include a similar lookup
when I'm, receiving the #N/A error? (If not I'll do it manually, but its a 31 sheet workbook). Also, when I change the index array below to lookup on another sheet does every array reference need the 'sheetname'! added or just in a strategic spot or too. "T. Valko" wrote: Ok, this should work... Array entered** : =INDEX($A$2:$A$100,SMALL(IF(ISTEXT($L$2:$L$100),IF (($L$2:$L$100<"")*($L$2:$L$100<"Resolved"),ROW(A $2:A$100))),ROWS(A$1:A1))-ROW(A$2)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Sorry, I'm a bit confused. Column L uses:=IF(AND($G2<"Resolved",$C2="SAP-SECURITY"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"") Current results include "In Progress", "Resolved", "On Hold", blank or the issue #N/A. Another column has (entered 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))) However, the #N/A causes the whole thing to return as #NA. When I entered:=IF(ISNA(MATCH($A2,'25'!$A$2:$A$100,0)),"N o Match",IF(AND($G2<"Resolved",$C2="SAP-SVC DESK"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"")) I received way too many "No Match" values. Do I change my Index formula to contain the three variables? ($G2<"Resolved",$C2="SAP-SECURITY",$N2="No Match") Or is there an easier way I'm missing because I really don't speak Excel very well? :) "T. Valko" wrote: We'll get this sorted out sooner or later! <g The "no match" of the lookup is a separate condition from $G4<"Resolved" and $C4="SAP-SECURITY". You can do this for the "no match": =IF(ISNA(MATCH($A4,'25'!$A$2:$A$100,0)),"No Match",......... The rest of the formula would be: ...IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),*what should go here*)) *what should go here* = what result do you want if/when $G4<"Resolved" and $C4="SAP-SECURITY" if FALSE? -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I appreciate you helping me with this. The problem is, that when there is no match in the look up it gives me a blank cell. The error is actuall preferable as it lets me know there's no match since thats part of what we're tracking. "T. Valko" wrote: Col L uses the following formula: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") Try changing that formula to: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY",ISNUMBER(MATCH($A4,'25'!$A$2:$A$100,0))) ,VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I'm not sure how to tell if column A is brought in as text or number, is there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
Almost perfect.
So, we're making progress! Is there a way to include a similar lookup when I'm, receiving the #N/A error? In other words, you want to do the same thing but this time return the data from col A where col L = #N/A. This one is actually easier since it's just a single condition to deal with. Array entered: =INDEX($A$2:$A$100,SMALL(IF(ISNA($L$2:$L$100),ROW( A$2:A$100)),ROWS(A$1:A1))-ROW(A$2)+1) when I change the index array below to lookup on another sheet does every array reference need the 'sheetname'! added or just in a strategic spot or too Technically, if all the data was on a different sheet you need to include the sheet name in "spots". For example, using the above formula with the data on Sheet1: =INDEX(Sheet1!$A$2:$A$100,SMALL(IF(ISNA(Sheet1!$L$ 2:$L$100),ROW(A$2:A$100)),ROWS(A$1:A1))-ROW(A$2)+1) You'll notice I didn't include the sheet name in any of the ROW functions. In both the ROW and ROWS functions the sheet name is irrelavent as to how these functions work. **However**, including the sheet name doesn't hurt anything and makes it easier to understand what the formula is doing. =INDEX(Sheet1!$A$2:$A$100,SMALL(IF(ISNA(Sheet1!$L$ 2:$L$100),ROW(Sheet1!A$2:A$100)),ROWS(A$1:A1))-ROW(Sheet1!A$2)+1) -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Almost perfect. 2 more questions: Is there a way to include a similar lookup when I'm, receiving the #N/A error? (If not I'll do it manually, but its a 31 sheet workbook). Also, when I change the index array below to lookup on another sheet does every array reference need the 'sheetname'! added or just in a strategic spot or too. "T. Valko" wrote: Ok, this should work... Array entered** : =INDEX($A$2:$A$100,SMALL(IF(ISTEXT($L$2:$L$100),IF (($L$2:$L$100<"")*($L$2:$L$100<"Resolved"),ROW(A $2:A$100))),ROWS(A$1:A1))-ROW(A$2)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Sorry, I'm a bit confused. Column L uses:=IF(AND($G2<"Resolved",$C2="SAP-SECURITY"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"") Current results include "In Progress", "Resolved", "On Hold", blank or the issue #N/A. Another column has (entered 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))) However, the #N/A causes the whole thing to return as #NA. When I entered:=IF(ISNA(MATCH($A2,'25'!$A$2:$A$100,0)),"N o Match",IF(AND($G2<"Resolved",$C2="SAP-SVC DESK"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"")) I received way too many "No Match" values. Do I change my Index formula to contain the three variables? ($G2<"Resolved",$C2="SAP-SECURITY",$N2="No Match") Or is there an easier way I'm missing because I really don't speak Excel very well? :) "T. Valko" wrote: We'll get this sorted out sooner or later! <g The "no match" of the lookup is a separate condition from $G4<"Resolved" and $C4="SAP-SECURITY". You can do this for the "no match": =IF(ISNA(MATCH($A4,'25'!$A$2:$A$100,0)),"No Match",......... The rest of the formula would be: ...IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),*what should go here*)) *what should go here* = what result do you want if/when $G4<"Resolved" and $C4="SAP-SECURITY" if FALSE? -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I appreciate you helping me with this. The problem is, that when there is no match in the look up it gives me a blank cell. The error is actuall preferable as it lets me know there's no match since thats part of what we're tracking. "T. Valko" wrote: Col L uses the following formula: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") Try changing that formula to: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY",ISNUMBER(MATCH($A4,'25'!$A$2:$A$100,0))) ,VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I'm not sure how to tell if column A is brought in as text or number, is there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
You are so awesome. let me know if you come to Vegas- I owe you a homemade
thank you cake. When I do the look up, some pages naturally have more items in the look up than others. How do I make the #NUM! error disappear? I'm guessing I use ISERR but I don't know where to plug it in. thanks. "T. Valko" wrote: Almost perfect. So, we're making progress! Is there a way to include a similar lookup when I'm, receiving the #N/A error? In other words, you want to do the same thing but this time return the data from col A where col L = #N/A. This one is actually easier since it's just a single condition to deal with. Array entered: =INDEX($A$2:$A$100,SMALL(IF(ISNA($L$2:$L$100),ROW( A$2:A$100)),ROWS(A$1:A1))-ROW(A$2)+1) when I change the index array below to lookup on another sheet does every array reference need the 'sheetname'! added or just in a strategic spot or too Technically, if all the data was on a different sheet you need to include the sheet name in "spots". For example, using the above formula with the data on Sheet1: =INDEX(Sheet1!$A$2:$A$100,SMALL(IF(ISNA(Sheet1!$L$ 2:$L$100),ROW(A$2:A$100)),ROWS(A$1:A1))-ROW(A$2)+1) You'll notice I didn't include the sheet name in any of the ROW functions. In both the ROW and ROWS functions the sheet name is irrelavent as to how these functions work. **However**, including the sheet name doesn't hurt anything and makes it easier to understand what the formula is doing. =INDEX(Sheet1!$A$2:$A$100,SMALL(IF(ISNA(Sheet1!$L$ 2:$L$100),ROW(Sheet1!A$2:A$100)),ROWS(A$1:A1))-ROW(Sheet1!A$2)+1) -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Almost perfect. 2 more questions: Is there a way to include a similar lookup when I'm, receiving the #N/A error? (If not I'll do it manually, but its a 31 sheet workbook). Also, when I change the index array below to lookup on another sheet does every array reference need the 'sheetname'! added or just in a strategic spot or too. "T. Valko" wrote: Ok, this should work... Array entered** : =INDEX($A$2:$A$100,SMALL(IF(ISTEXT($L$2:$L$100),IF (($L$2:$L$100<"")*($L$2:$L$100<"Resolved"),ROW(A $2:A$100))),ROWS(A$1:A1))-ROW(A$2)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Sorry, I'm a bit confused. Column L uses:=IF(AND($G2<"Resolved",$C2="SAP-SECURITY"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"") Current results include "In Progress", "Resolved", "On Hold", blank or the issue #N/A. Another column has (entered 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))) However, the #N/A causes the whole thing to return as #NA. When I entered:=IF(ISNA(MATCH($A2,'25'!$A$2:$A$100,0)),"N o Match",IF(AND($G2<"Resolved",$C2="SAP-SVC DESK"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"")) I received way too many "No Match" values. Do I change my Index formula to contain the three variables? ($G2<"Resolved",$C2="SAP-SECURITY",$N2="No Match") Or is there an easier way I'm missing because I really don't speak Excel very well? :) "T. Valko" wrote: We'll get this sorted out sooner or later! <g The "no match" of the lookup is a separate condition from $G4<"Resolved" and $C4="SAP-SECURITY". You can do this for the "no match": =IF(ISNA(MATCH($A4,'25'!$A$2:$A$100,0)),"No Match",......... The rest of the formula would be: ...IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),*what should go here*)) *what should go here* = what result do you want if/when $G4<"Resolved" and $C4="SAP-SECURITY" if FALSE? -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I appreciate you helping me with this. The problem is, that when there is no match in the look up it gives me a blank cell. The error is actuall preferable as it lets me know there's no match since thats part of what we're tracking. "T. Valko" wrote: Col L uses the following formula: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") Try changing that formula to: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY",ISNUMBER(MATCH($A4,'25'!$A$2:$A$100,0))) ,VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I'm not sure how to tell if column A is brought in as text or number, is there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with INDEX lookup
There are a few ways to do this and the best option to use depends on the
type of data the lookup formula is returning and how complex the lookup formula is. The generic method that works in all applications is to wrap the portion of the formula that actually generates the error in an ISERROR function. The drawback to this method is that it's not very efficient and it effectively makes the formula twice as long. For example, using this formula: =INDEX($A$2:$A$100,SMALL(IF(ISNA($L$2:$L$100),ROW( A$2:A$100)),ROWS(A$1:A1))-ROW(A$2)+1) Trapping the errors: =IF(ISERROR(SMALL(IF(ISNA($L$2:$L$100),ROW(A$2:A$1 00)),ROWS(A$1:A1))-ROW(A$2)+1),"",INDEX($A$2:$A$100,SMALL(IF(ISNA($L$ 2:$L$100),ROW(A$2:A$100)),ROWS(A$1:A1))-ROW(A$2)+1)) And if you have to include sheet names then that makes it even longer! (which is one reason to use the shortest most concise sheet names you can think of!) A more efficient method is to use a formula in another cell to count how many records meet the condition and then compare that result to the number of cells the formula is copied to. For example: In the above formula the condition is if there's a #N/A error in the range. So, we use a separate cell with a formula that counts how many #N/A errors are in the range: =SUMPRODUCT(--(ISNA(L2:L100))) Then we compare that number to the number of cells the formula is copied to. For every cell the formula is copied to this expression returns that count: ROWS(A$1:A1). So, we compare ROWS(A$1:A1) to =SUMPRODUCT(--(ISNA(L2:L100))). Here's how that's done: Let's assume: B1 holds the formula: =SUMPRODUCT(--(ISNA(L2:L100))) To incorporate that into the error trap: =IF(ROWS(A$1:A1)B$1,"",INDEX($A$2:$A$100,SMALL(IF (ISNA($L$2:$L$100),ROW(A$2:A$100)),ROWS(A$1:A1))-ROW(A$2)+1)) As you can see that's significantly shorter than the generic method and it's much more efficient overall. Just remember, all of the lookup formulas are array formulas so they need to be array entered! -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... You are so awesome. let me know if you come to Vegas- I owe you a homemade thank you cake. When I do the look up, some pages naturally have more items in the look up than others. How do I make the #NUM! error disappear? I'm guessing I use ISERR but I don't know where to plug it in. thanks. "T. Valko" wrote: Almost perfect. So, we're making progress! Is there a way to include a similar lookup when I'm, receiving the #N/A error? In other words, you want to do the same thing but this time return the data from col A where col L = #N/A. This one is actually easier since it's just a single condition to deal with. Array entered: =INDEX($A$2:$A$100,SMALL(IF(ISNA($L$2:$L$100),ROW( A$2:A$100)),ROWS(A$1:A1))-ROW(A$2)+1) when I change the index array below to lookup on another sheet does every array reference need the 'sheetname'! added or just in a strategic spot or too Technically, if all the data was on a different sheet you need to include the sheet name in "spots". For example, using the above formula with the data on Sheet1: =INDEX(Sheet1!$A$2:$A$100,SMALL(IF(ISNA(Sheet1!$L$ 2:$L$100),ROW(A$2:A$100)),ROWS(A$1:A1))-ROW(A$2)+1) You'll notice I didn't include the sheet name in any of the ROW functions. In both the ROW and ROWS functions the sheet name is irrelavent as to how these functions work. **However**, including the sheet name doesn't hurt anything and makes it easier to understand what the formula is doing. =INDEX(Sheet1!$A$2:$A$100,SMALL(IF(ISNA(Sheet1!$L$ 2:$L$100),ROW(Sheet1!A$2:A$100)),ROWS(A$1:A1))-ROW(Sheet1!A$2)+1) -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Almost perfect. 2 more questions: Is there a way to include a similar lookup when I'm, receiving the #N/A error? (If not I'll do it manually, but its a 31 sheet workbook). Also, when I change the index array below to lookup on another sheet does every array reference need the 'sheetname'! added or just in a strategic spot or too. "T. Valko" wrote: Ok, this should work... Array entered** : =INDEX($A$2:$A$100,SMALL(IF(ISTEXT($L$2:$L$100),IF (($L$2:$L$100<"")*($L$2:$L$100<"Resolved"),ROW(A $2:A$100))),ROWS(A$1:A1))-ROW(A$2)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... Sorry, I'm a bit confused. Column L uses:=IF(AND($G2<"Resolved",$C2="SAP-SECURITY"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"") Current results include "In Progress", "Resolved", "On Hold", blank or the issue #N/A. Another column has (entered 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))) However, the #N/A causes the whole thing to return as #NA. When I entered:=IF(ISNA(MATCH($A2,'25'!$A$2:$A$100,0)),"N o Match",IF(AND($G2<"Resolved",$C2="SAP-SVC DESK"),VLOOKUP($A2,'25'!$A$2:$J$100,7,0),"")) I received way too many "No Match" values. Do I change my Index formula to contain the three variables? ($G2<"Resolved",$C2="SAP-SECURITY",$N2="No Match") Or is there an easier way I'm missing because I really don't speak Excel very well? :) "T. Valko" wrote: We'll get this sorted out sooner or later! <g The "no match" of the lookup is a separate condition from $G4<"Resolved" and $C4="SAP-SECURITY". You can do this for the "no match": =IF(ISNA(MATCH($A4,'25'!$A$2:$A$100,0)),"No Match",......... The rest of the formula would be: ...IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),*what should go here*)) *what should go here* = what result do you want if/when $G4<"Resolved" and $C4="SAP-SECURITY" if FALSE? -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I appreciate you helping me with this. The problem is, that when there is no match in the look up it gives me a blank cell. The error is actuall preferable as it lets me know there's no match since thats part of what we're tracking. "T. Valko" wrote: Col L uses the following formula: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY"),VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") Try changing that formula to: =IF(AND($G4<"Resolved",$C4="SAP-SECURITY",ISNUMBER(MATCH($A4,'25'!$A$2:$A$100,0))) ,VLOOKUP($A4,'25'!$A$2:$J$100,7,0),"") -- Biff Microsoft Excel MVP "Vegas Sara" wrote in message ... I'm not sure how to tell if column A is brought in as text or number, is there a way to be sure? Is it just formating the cells? It looks like a number. I have no problem with it being text as it won't be used in any calculations. I'm using 2003 SP2. Over the weekend I thought to use an extra column with: =IF(AND($L$2:$L$100<"", $L$2:$L$100<"Resolved"), "Yes",""), then just changing the previous formula to look for the yes, but it too is returning #N/A when that is in in the lookup cell... "T. Valko" wrote: 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) |