Codes for Text in Excel Worksheet

**wvollbon** · 06-05-2011, 01:44 AM

Hi, hopefully someone can help me!

I was hoping to devise a way to be able to enter a code to display a certain word in a worksheet.

For example.

Entering for example 10 would return blue
Entering for example 11 would return green
Entering for example 15 would also return green

However for statistical purposes it was hoped that I would be able to count the number of times 15 or 11 was entered and not the word green.

I have attempted to find this out through many forums however cannot seem to figure it out. Hopefully someone could lend a hand!

**teylyn** · 06-05-2011, 02:27 AM

Hello,

set up a table in your workbook that lists the codes and the text side by side in two separate columns

A   B
10 blue
11 green
15 green

In another area of the spreadsheet, for example in D1 enter the number. In E1 enter this Vlookup formula

=Vlookup(D1,$A$1:$B$3,2,false)

E1 will now contain the text. Copy E1 down and enter more codes in column D.

You can then perform a count of codes in column D to find out how often each code has been used.

This approach can be refined with error trapping to avoid N/A errors in column E if the cell in column D is empty.

cheers,