Matching One Cell to a List of Cells

[Misterchipper]

I have a list of approximately 15 states that are my list to match to.

Then I have several thousands of rows of data of people, addresses, and states. If one of these records is found in one of these 15 states, I would like to flag the record.

I know how to do this easily with vlookups and if(or( statements--however, I was wondering if anyone knew a simpler, faster, easier way to do it....maybe with arrays?

Help--vlookups are slowing down my computer..

 

[texasalynn]

why not a conditional format instead

 

[Misterchipper]

Fantastic. How do I go about doing that?

Thanks again for your help

 

[texasalynn]

highlight the column you want to have hightlighted, then select
format - Conditional Formatting - click dropdown to formula is
then in the box type in
=or("Texas", "Idaho", "Colorado") add whatever states you are wanting
then select the format pattern of the color and click Ok - Ok

 

[Misterchipper]

Thanks!

How would I do that with arrays/formulas.

Again I have one set of data that is about 10000 rows. One of these columns is state.

Then I have a list of 15 states that are problem states. I would like some formula/array to flag these 15 states.

 

[Aladin Akyurek]

I have a list of approximately 15 states that are my list to match to.

Then I have several thousands of rows of data of people, addresses, and states. If one of these records is found in one of these 15 states, I would like to flag the record.

I know how to do this easily with vlookups and if(or( statements--however, I was wondering if anyone knew a simpler, faster, easier way to do it....maybe with arrays?

Help--vlookups are slowing down my computer..

Sort the range with 15 states in ascending order and name this range StateList.

Let E2 house the state from the first record to flag...

F2, just enter and copy down:

=(LOOKUP(E2,StateList)=E2)+0

This flags every record as either 1 (a hit) or as 0 (a miss) and does so in a reasonably fast fashion.

 

[Misterchipper]

Jackpot!