Multiple IF statement replacements?

[SJ316]

I need some help and don't know if it will be possible to get it to do
what I need. I am automating all my paperwork I do every morning and I
need a cell on a worksheet to decypher people's initials and translate
it to a name. here is a sample of what I have.

=IF('INDIVIDUAL TARGET'!A21="DH","Diane",IF('INDIVIDUAL
TARGET'!A21="LT","Lisa",IF('INDIVIDUAL
TARGET'!A21="LB","Linda",IF('INDIVIDUAL
TARGET'!A21="RN","Rae",IF('INDIVIDUAL
TARGET'!A21="LM","Liz",IF('INDIVIDUAL
TARGET'!A21="KC","Kathie",IF('INDIVIDUAL
TARGET'!A21="GL","Gladys",IF('INDIVIDUAL TARGET'!A21="DC","Dorothy","
"))))))))

But I hit the seven IF limit. I have a maxium of 18 workers. The table
that lists the intials are or a seperate worksheet within the file and
are in a single column.

I know this probably looks messed up. but any help will be
appreciated.

Thanks,
Steven J

 

[Ken Wright]

use VLOOKUP

with your initials in say A1:A20 and the corresponding names in B1:B20 and your
lookup value in A21, use:-

=VLOOKUP(A21,A1:B20,2,0)

This will look for whatever value is in A21, and it will look for it in the
leftmost column of the range A1:B20, and when it finds it it will return the
corresponding data from the 2nd column in that range. the last and 4th optional
argument of 0 menas it must find an exact match for it to return anything (There
are times when close enough will do).

 

[John Wilson]

Steven,

You need a lookup table

Example....
For this example, the formulas are located on sheet1
There's a sheet named "Target" with A21 = LT
There's a sheet named "List" where I put the
lookup table. These can all be moved if referenced
correctly.
Okay...in the sheet labeled List, I entered the following
starting from A1 to B3 (you can expand this list as much
as you need to).
Col A Col B
DH Dianne
LT Lisa
LB Linda

Now from anywhere in sheet, I can use the following
formula:

=VLOOKUP(Target!A21,List!A1:B3,2,FALSE)
The result will be "Lisa".

For more help, check Excels help for "Lookup"

John

 

[Dan E]

Steven,

If you've got a separate table listing the initials, you can use a
VLOOKUP to retrieve the name given the initials. If it isn't already
set up in the following manner, change it to match the format.
(columns A & B on sheet "INITIALS" for example):
DH Diane
LB Linda
RN Rae
LN Liz
KC Kathie
etc...

Now use the formula

=VLOOKUP('INDIVIDUAL TARGET'!A21,INITIALS!A1:B20,2,FALSE)

Search VLOOKUP in the Help menu to get an idea of how the
function works.

Other possibilities for lookups are INDEX/MATCH and LOOKUP

Dan E

 

[SJ316]

Thank you! I couldn't figure it out with the MS Help system, but you
explation cleared it up for me. Works great, should save me roughly a
hour's worth of work every morning when this monster sheet i
finished!

Steven J.

use VLOOKUP

with your initials in say A1:A20 and the corresponding names i
B1:B20 and your
lookup value in A21, use:-

=VLOOKUP(A21,A1:B20,2,0)

This will look for whatever value is in A21, and it will look for i
in the
leftmost column of the range A1:B20, and when it finds it it wil
return the
corresponding data from the 2nd column in that range. the last an
4th optional
argument of 0 menas it must find an exact match for it to retur
anything (There
are times when close enough will do).

 

[Ken Wright]

A pleasure, and thanks for the feedback. Just post back if you have any
problems.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Best wishes to all, and hope for a good New year
----------------------------------------------------------------------------

Thank you! I couldn't figure it out with the MS Help system, but your
explation cleared it up for me. Works great, should save me roughly an
hour's worth of work every morning when this monster sheet is
finished!

Steven J.

use VLOOKUP

with your initials in say A1:A20 and the corresponding names in
B1:B20 and your
lookup value in A21, use:-

=VLOOKUP(A21,A1:B20,2,0)

This will look for whatever value is in A21, and it will look for it
in the
leftmost column of the range A1:B20, and when it finds it it will
return the
corresponding data from the 2nd column in that range. the last and
4th optional
argument of 0 menas it must find an exact match for it to return
anything (There
are times when close enough will do).