Search code examples
excelif-statementstatements

If Statements - Populating cell data based on zip code list

I have a list of zip codes and I'd like to associate a color code to a particular zip code. I have the list of colors that belong to the zip codes and want a formula that will populate the color code to the zip codes as I'm constantly looking at new data and having to fill in the data.

This is what I have so far 

=IF(B3=I3:I20, "Blue", IF(B2=I21:I34, "Orange", IF(B3=I35:I56, "Purple", IF(B3=I57:I74, "Yellow"))))

but it only worked for one cell and the rest are showing errors. B3 is the cell where the zip code in question is and column I is the column where I have all zip codes sorted by color.

name

Solution

  • Change your comparison from

    B3=I3:I20

    to

    ISNUMBER(MATCH(B3,$I$3:$I$20,0))

    Do the above for all you logical comparison. You will wind up with:

    =IF(ISNUMBER(MATCH(B3,$I$3:$I$20,0)), "Blue", 
IF(ISNUMBER(MATCH(B3,$I$21:$I$34,0)), "Orange", 
IF(ISNUMBER(MATCH(B3,$I$35:$I$56,0)), "Purple", 
IF(ISNUMBER(MATCH(B3,$I$57:$I$74,0)), "Yellow","Not Found"))))

    I added what to do if your last IF check came up false.