Download the Excel VLookup multiple column example mentioned below

Say you need to recode several columns of data with the same LOOKUP values.  You can do this by simply copying the table you want recoded (in the example file we are copying the RawData sheet to another sheet we have named ‘KOOS-recode’), and then applying a function shown below to the columns needing to be recoded in the KOOS-recode table.

The recode function we are using is listed below:
=VLOOKUP(INDIRECT("RawData["&INDIRECT(CONCATENATE( SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""),1))&"]"), 'KOOS-lookup-recode'!$A$1:$B$5, 2, FALSE)

The logic of this function is simple.  We use two copies of the same table since we can not directly run this function on the table data we want recoded/changed.  So we use the first table as our original ‘raw’ data.  This function gets the name of the column it is being run under (and the cell) and gets the corresponding cell value from the original table, then runs a VLOOKUP on the value to give you the recoded data.

I arrived at this function by first figuring out how to get the currently selected cell names and the currently selected column name.  I figured once I had that I could figure out how to get the corresponding value from the original table in order to transform it.  Somehow I was able to get the syntax correct (which is convoluted to say the least).

Some other Excel functions to get current table and cell information (used in the building of the function above)

Please note, the column/table header names must be the same in both tables

Get the current cell address
=CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""),ROW())

Get the currently selected column header (value)
=INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""),1))

Get values from another table (RawData) to pull into your current table
=INDIRECT("RawData["&INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""),1))&"]")