Returning Text values from an Array

  • Thread starter Thread starter Jim_Jammy
  • Start date Start date
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
 
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
 
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
 
Back
Top