Formula:"rename" a code in a spreadsheet

[jianssen]

Hi,

I have problems with creating a formula that will return a person'
name whenever a certain code appears.

Case:
In one column i have codes (i.e. Aa, AAc AbAA, aAx etc. which appear
at least twice (the list is sorted)). In another column I have number
(or empty cells) and in the last column I have names.

Problem:
For every code that has an empty cell next I want to return an empt
cell. For every code that has a cell containing numbers next to it
want to return the name that corresponds to the code.
The spread sheet has over 2000 entries.

Solution:
???? Have experimented with IFs but are not able to come up wit
something that works...

Regards,
Jorge

 

[Dave Hawley]

Hi Jorgen

VLOOKUP should do the trick.

Let's say your codes are in Column A starting from A1. In Column B, on
corresponding rows, are the ascociated names. Cell D1 contains a code
that has been entered. Use the formula below

=IF(OR(COUNTIF(D1,$A$1:$A$5000),D1=""),"",VLOOKUP(D1,$A$1:$A$5000,2,FALS
E))

You make life easier by creating a unique list of codes via
Data>Filter>Advanced Filter. Then select the cells that Codes will be
entered and go to Data>Validation-List and reference your unique range
of codes.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****