Quote:
Originally Posted by Karen615
... I’ve been trying to figure this out but get stumped at VLOOKUP. I have used VLOOKUP before but cannot get this to work with a drop-down...
|
You don't use VLOOKUP in Data Validation. In Excel, you need to use 2 columns for what you want to achieve. In first you select State code, and in second the formula retrieves according State name from States table. (In second column you probably want to check for State code not being an empty string too!)
Logit:
Excel has limitation for number of nested IF's (7 when I remember properly). So for 50 States this doesn't work. There is a workaround using additional IF's to group States to smaller subgroups (for every subgroup level the number of nested IF's you can use will be reduced by 1 - so when using 2 subgroups, you can nest 2*6=12 IF's, when using 2*2 subgroups, then you can nest 4*5=20 IF's, etc.), but this formula will be very long and clumsy, and 50 will be anyway out of reach.
The another workaround is use formula like
Code:
=IF(StateCode=CodeValue1,TextValue1, "") & IF(StateCode = CodeValue2, TextValue2, "") & ...
No limitations there except the fomula length, but ...