Answer
Your Comparison Operators sheet should look like so:
Explanation
Look at the "Rank & Pro Competition Info" table.
Notice how all the values are vertically organized? And that we'll be looking up specific values in this table?
This should signify to you that we'll be using the VLOOKUP function in cell D10 to find Carl's Rank Description.
Below is the template for the VLOOKUP function:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
First, our lookup_value is going to be the Rank item in C10.
=VLOOKUP(C10, table_array, col_index_num, [range_lookup])
Next, our table_array is the table that we're going to be searching for this value in. It's important to note that this table must have the lookup_value in the first column, otherwise the VLOOKUP function won't know where to look for it! Therefore, we'll designate our table_array as G16:I21.
=VLOOKUP(C10, G16:I21, col_index_num, [range_lookup])
The col_index_num is the column number for the value that we want to show in cell D10, which is the Rank Description. This occurs in the 2nd column, so we'll place 2 here.
=VLOOKUP(C10, G16:I21, 2, [range_lookup])
Lastly, for [range_lookup] we're going to enter FALSE, that way our VLOOKUP only looks for exact matches, not approximate matches of our lookup_value.
=VLOOKUP(C10, G16:I21, 2, FALSE)
When we plug this into Excel, we get the following value:
Now, we need to copy this through D16.
Hmm... why are we getting an error?
Because when we're copying this VLOOKUP down through D16, our table_array is shifting down by one cell with each row we copy it through. Therefore, we must absolutely reference the table_array in cell D10.
=VLOOKUP(C10, $G$16:$I$21, 2, FALSE)
Now when we add our absolute cell reference in D10 and copy it down through D16, it successfully shows each person's Rank Description!
