The answer is, arguably, one of the most powerful functions you can create in excel. If you're new to excel, this is going to be a lot to swallow.
Given your exact example, you may be able to get away with using
VLOOKUP. But I'm assuming the reality of the situation is a bit more complicated than your example so I'm giving you something that can handle pretty much any situation. It's a combination of the
INDEX function and the
I created two tables:
TableA:Row1(mammal, ferret, Mr. Whiskers)
TableB:Row1(cat, dog, mouse)
TableB:Row2(ferret, iguana, turtle)
TableB:Row3(horse, cow, pig)
In TableA:ColumnD I put the following formula:
First, lets look just at
INDEX takes 3 arguments, the range you want to search over, the row of the value you want, and the column of the value you want. The range you want to search over is specified by
TableB! says go to another sheet named TableB
The dollar signs in front of the column and row say "these are absolute, do not automatically increment when filling the formula down (or right, left, up)"
1 at the end says "the first column in the range" which in our case happens to be A and contains cat, ferret, and horse.
Now, to get the row, we need to use another function called
MATCH to match a value from the row we are currently working on.
MATCH also takes 3 values. The first is the value you are searching for, the second is a single column to search in, and the final is how to match (in this case we way FALSE for "exact match").
We want to match the value that is in TableA:ColumnB for the current row. So the first value is $B1. Which says the column B is absolute, don't increment it. But the row should increment to match whatever row we are on when we fill down so it does not have the preceding dollar sign.
The column to search in is the A column on TableB and is absolute so it won't increment.
If we then go to TableA:ColumnE and put in:
And in TableA:ColumnF we put:
We get the desired result. Notice the only difference in the formulas is the final number specifying the column to return.
If we change TableA:ColumnB to "ferret" then Column D changes to ferret, Column E changes to iguana and Column F changes to turtle.
Finally, select the cells in row 1 under D, E, and F. Drag your mouse down to cover all the rows necessary and press Ctrl+D which will fill the formula down through all the rows, incrementing the search term in the
MATCH function to search for the value in column B of each row.