If Statements - Populating cell data based on zip code list

Question

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.

Answer 1

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.

Answer 2

Sounds like you need to use VLOOKUP. Something like: VLOOKUP(B3,$I$1:$J$500,2,0)

Where the second argument ($I$1:$J$500) represents range where your zip codes/colors are located (zip codes in column I, colors in column J). Sort this list by zip code and drag the formula down your list of new numbers.

If you want to get really fancy you could make your list of codes/colors a named range.