Change Value of cell based on value in another cell (many possible values)

[acseric]

I am trying to set values in all of the cells in a given column, depending on the value of another column.

In this case Column AJ has 2 character state abbreviations. I want to set Column C a different hard coded value based on values in AJ

So, if column AJ contains (AK, AZ, AR, CA, CO, ID, IA, KS, LA, MN, MO, or MT) set C = "Joe"
or if column AJ contains (NE, NV, NM, ND, OK, OR, SD, TX, UT, WA, or WY) set C = "Sally"
or if column AJ contains (AL, CT, DE, FL, GA, IN, IL, KY, ME, MD, or MA) set C = "Pete"

Is there a better way than creating a giant nested IF?

Any help is greatly appreciated.

Eric

 

[Eric W]

I'd create a 2-column table with the state IDs and the names in it, like:

	A	B
1	AK	Joe
2	AR	Joe
3	AZ	Joe
4	NE	Sally
5	AL	Pete

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

etc.

Then in column C, just use a VLOOKUP:

=VLOOKUP(AJ2,$A$1:$B$50,2,FALSE)

 

[acseric]

Is there a way to do this with VBA?

I would like to create a macro so I can reuse it. Also, can the lookup table be on Sheet2, and have the macro look up the values based on column A and B on the second sheet?

Thanks!

Eric

 

[Eric W]

First, you can use the VLOOKUP with a range on another sheet:

=VLOOKUP(AJ2,Sheet2!$A$1:$B$50,2,FALSE)

Second, you can do this with VBA, but there's no reason to. One way is to write a UDF (User-defined function), which would just call WorksheetFunction.Vlookup, so all you're doing is adding overhead to get the same thing done. Another way would be to write a macro that does all of column C/AJ at one time, reading down column AJ, getting the value, looking up the value from Sheet2, then inserting in it column C, then continuing down column AJ until we find an empty cell. But you'd have to initiate it with a hot key, or the macro selector, or a button, or an event handler. Either more hassle, or more overhead.

I'm a big fan of VBA, but when you can get your work done with basic built-in functions, that's usually the way to go.

Hope this helps!