How do I get text in cell a depending on text in cell b?

[mduddridge]

I have a spreadsheet into which people's usernames are entered (cell b),
(e.g. "APPLESEEDJ"). I want cell a, on the same spreadsheet, to show
the person's name, (e.g. "Johnny Appleseed").

The formula
=IF([Cell b]="APPLESEEDJ","Johnny Appleseed","Enter Username")
works perfectly, but only if the entry in cell b is APPLESEEDJ or no
data/any other data. In all, cell b will have one of ten (10)
Usernames or no data.

I'm sure there's an easy way to do this, but I've been playing around
with the Functions and nesting for an over hour and am no nearer to
making this happen.

Thanks, in advance for all help offered.

 

[Frank Kabel]

Hi
not quite sure but you may need a lookup table:
- setup a separate sheet (call it for example 'lookup') and enter the
shortname in column A and the real name in column B:
A B
APPLESEEDJ Johnny Appleseed
.....

Now use the following formula
=IF(ISNA(VLOOKUP(B1,'lookup'!$A$1:$B$20,2,0),"Enter the user
name",VLOOKUP(B1,'lookup'!$A$1:$B$20,2,0))

 

[mduddridge]

*<snip>
Now use the following formula
=IF(ISNA(VLOOKUP(B1,'lookup'!$A$1:$B$20,2,0),"Enter the user
name",VLOOKUP(B1,'lookup'!$A$1:$B$20,2,0))
</snip>*

Thanks, Frank.

It has helped some, but it still does not have the desired effect.

 

[Frank Kabel]

Hi
what is exactly missing? and what has worked?

 

[mduddridge]

Hi Frank,

To follow the example formula you posted
VLOOKUP(B1,'lookup'!$A$1:$B$20,2,0) works superbly, returning eithe
the person's name corresponding to the username in cell b. It seem
that adding the extra formulae with the ISNA function is the problem.

That said using the simple VLOOKUP formula with the ISNA function work
too, to the extent that TRUE or FALSE appears in the cell containin
the formula.

I'm just going to go with the VLOOKUP formula and manually chang
anything that gives a #N/A result.

Again, thanks for all your help. :

 

[Frank Kabel]

Hi
try (I missed a closing bracket9:
=IF(ISNA(VLOOKUP(B1,'lookup'!$A$1:$B$20,2,0)),"Enter the user
name",VLOOKUP(B1,'lookup'!$A$1:$B$20,2,0))

 

[mduddridge]

OMG!

Despite posting my last response, I thought I'd mess around just
little longer and I cracked it!

The formula is:
=IF(ISNA(VLOOKUP(E19,'lookup'!A2:B11,2,0)),"Enter th
username",(VLOOKUP(E19,'lookup'!A2:B11,2,0)))

After all that, it just needed some extra ('s & )'s

 

[mduddridge]

Frank,

Thanks again for the help. I didn't realise you had posted your las
response until after I'd posted mine!

See... this is what the Internet should be more about: a German i
Germany and a Welshman in the U.S., putting their heads together t
sort out a problem. Maybe the Governments around the world should b
run through Newsgroup postings! *LOL