What lookup function to use?

[maplewood]

I have a long list of user accounts to parse as either Type A or Type B. If they are from any of about 75 named locations they are Type A. If they are not they are Type B.

What function would let me identify each current (and new) account? Vlookup? (Not that I have any clue as how to use that.)

[JBeaucaire]

Assuming the individual account locations are in column A starting at A2.
Assuming the 75 locations are listed in column AA.

In B2 put this formula:
=IF(ISNUMBER(MATCH(A2, AA:AA, 0)), "Type A", "Type B")

[maplewood]

that's fantastic! thanks.

[maplewood]

Now I'm getting fancy:

I want to move the list of areas from the same worksheet to a different worksheet. (That happens to be named "List").
I've tried various versions of "List!A:List!A" to reference the "off page" list but haven't cracked it yet. What's the correct text string?

And if I could have a third value in the lookup (Area A, or Area B or Area C) that would be icing on the cake.

[JBeaucaire]

Try:

=IF(ISNUMBER(MATCH(A2, List!AA:AA, 0)), "Type A", "Type B")


You'll have to fully explain what your parameters are for Area A, Area B, Area C.

[maplewood]

Again, thanks for this great help.

There are in fact just two "Areas" but some records don't have data in the necessary field (the field is optional for the user). Rather than report/categorize them as A or B (they seem to be defaulting to B) I'd report them as "none" or whatever.

[JBeaucaire]

So, if A2 were blank:

=IF(A2="", "none", IF(ISNUMBER(MATCH(A2, List!AA:AA, 0)), "Type A", "Type B"))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.