Here is a standard issue – you have several Excel tables that stores different aspects about the same thing. And you need to jump between those spreadsheets back and forward. A lot of fun, but not very effective 🙂 .
In SQL you can do joins. Excel is powerful enough to do the same and with less lines. MATCH and INDEX functions will help us. MATCH finds the place of occurrence and INDEX uses it to take the value.
Suppose I have two sheets: Sheet1 and Sheet2;
Column A in Sheet1 has similar values as column A in Sheet2;
My goal is to collect values located in column B in Sheet2 and map them with server names in Sheet1.
Place this command into C2 and drag it:
MATCH(Sheet1!A2,Sheet2!$A$2:$A$6,0) – this command finds the place of Sheet1!A2 value in the list Sheet2!$A$2:$A$6
INDEX() – takes the link to a value from the column that I’m interested in Sheet2!$B$2:$B$6 based on the value returned by MATCH function and gives the index.
I’m using $ signs to fix the range.