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.