Formula:"rename" a code in a spreadsheet

  • Thread starter Thread starter jianssen
  • Start date Start date
J

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
 
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 *****
 
Back
Top