Have you ever had list of status flags that have a meaning and within excel be able to change the number for the actual meaning? Then this is for you!

Assume you have this data:

Customer Id Payment Type
1 2
2 0
3 1

And you have a set of Payment Types:

Payment Type Code Payment Description
0 Cash
1 Eftpos
2 Credit Card

If you have this data in an Excel spreadsheet you can use the VLOOKUP function to translate these numbers into meanings.

The VLOOKUP function is defined as VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) where:

  • lookup_value: the id that is to be looked up
  • table_array: the table which holds the key and the value (can be on another sheet)
  • col_index_num: the column number within the table which holds the value
  • range_lookup: boolean to specify if ‘approximate’ values should be looked up

Therefore a simple formula could be: =VLOOKUP(B15, A26:B29, 2, FALSE)

If you need to perform this action on a whole table of data and need to keep it in a constant column, and the lookup table is on another sheet, this can be used: =VLOOKUP(B2,Sheet2!$A$2:$B$5, 2, FALSE)

I cannot claim the credit for this albeit simple and well-documented function, all credit goes to Phil Wheeler