Returning Text values from an Array

J

Jim_Jammy

I have a Column of Account codes that I want to label with a text cod
if they exist in another range. I can create a 1 or 0 label by usin
the Sum function in the array (seen below). However I don't reall
want this as I want to return a text value (that I'll put in Column D
replacing the 1 currently there) and nothing if it doesn't exist.

{=SUM(IF(CW7=$DL$7:$DL$32,$DN$7:$DN$32))}

Really I think a vlookup function is suitable as part of any array bu
can't get that to work as I need to return nothing if the code isn't i
the list - Hence the if in the existing array- I don't want lower code
to have an error or higher codes to be labeled with a 1.

I'm sure it's not difficult but can't think of a suitable function
Any ideas
 
B

BrianB

The formula is like :-
=IF(ISERROR(VLOOKUP(A1,$B$2:$Z$500,2,FALSE)),"",VLOOKUP(A1,$B$2:$Z$500,2,FALSE))

ie IF(ISERROR (VLOOKUP ... empty string, otherwise VLOOKUP
 
J

Jim_Jammy

Thanks Brian - I had to add another If statment to your solution so tha
account codes greater than the last one in the lookup table wer
returned as blanks (rather than the last value in the second column)
I've posted it below for reference.

=IF(CW7>$DL$32,"",IF(ISERROR(VLOOKUP(CW7,$DL$7:$DM$32,2,FALSE)),"",VLOOKUP(CW7,$DL$7:$DM$32,2,FALSE))
)

Thanks again
 
Top