Automatic classification based on a table of relationships or typology

Last time, I talked about using VLOOKUP to automatically assign the grade equivalent of a raw score. This time, we will use VLOOKUP to classify non-numerical items.

Let's say that you have a table as follows:

Power Plant	Capacity (In MW)	Energy Resource	Fossil or Renewable?
APEC	50	Coal
Bacman	130	Geothermal
Bajada DPP	58.7	Oil Based
Bakun	70	Hydroelectric
Bangui Wind Power	33	Wind
Botocan	22.8	Hydroelectric
Calaca	600	Coal
Calibu DPP	30	Oil Based
Caliraya	35	Hydroelectric
Casa Bioenergy	15	Biomass
Casecnan (NIA)	165	Hydroelectric
CEDC Coal	246	Coal
CEMEX	66	Oil Based
Crystal Sugar	35.9	Biomass
DESCO Natural Gas	1	Natural Gas
First Farmers	21	Biomass

So you want the spreadsheet to enter in the fourth column if the power plant is renewable or fossil fueled. Assuming you know that  fossil means power plant using coal, oil-based or natural gas, and renewable uses everything else, you can create the following reference table (again, I recommend that you use a separate sheet within the same file):

Biomass	Renewable
Coal	Fossil
Geothermal	Renewable
Hydropower	Renewable
Natural Gas	Fossil
Oil	Fossil
Solar	Renewable
Wind	Renewable

You want to tell the program to do the following in your list of power plants:

1. Search for the type of energy resource in the list of power plants in the reference table, then
2. Enter the corresponding type of energy resource.

To do that, you type the following in the cell in the "Fossil or Renewable?" column:

=VLOOKUP(Cell_in_EnergyResourceRow, Range_of_ReferenceTable, Column_Number_of_Types_in_ReferenceTable)

Just do this in one column, change the references in the reference table to absolute, then copy the formula to the other cells in the "Fossil or Renewable?" column.

For reference, you can see this file.

Note: Your reference table may have multiple columns, because something may be classified in more than one way. For example, the power plant may be classified also based on if the energy resource if clean (all renewable energy plus natural gas) and traditional. To do this, just add the third column in the reference table, use VLOOKUP, and in the list, indicate the third column as the column number to be used.