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.