Replace contents of Excel column with realtional data from another column/sheet?

Question

I have two excel sheets that have been exported from MySQL, with relational data in them, and I need to replace the reference ID's with the actual data from the relative column.

For example, in one table:

+----------------------------------------+
| ID  | Name | ForeignKey1 | ForeignKey2 |
+----------------------------------------+
| 1   | Bob  | 1           | 2           |
| 2   | Dave | 2           | 1           |
| 3   | Mary | 3           | 4           |
| 4   | Jane | 4           | 1           |
| etc........

Then in the other table, that the ForeignKeys are referencing:

+---------------------+
| ID | Name           |
+---------------------+
| 1  | Banana         |
| 2  | Apple          |
| 3  | Cat            |
| 4  | Dog            |
| etc.......

Is there an easy way to replace the Foreign key with the relative data in excel, so that my data looks like this?

+----------------------------------------+
| ID  | Name | ForeignKey1 | ForeignKey2 |
+----------------------------------------+
| 1   | Bob  | Banana      | Apple       |
| 2   | Dave | Apple       | Banana      |
| 3   | Mary | Cat         | Dog         |
| 4   | Jane | Dog         | Banana      |
| etc........

Answer 1

This can be accomplished with a vlookup formula:

=VLOOKUP(Lookup Value , Table, Column, Exact Match or Approximate Match)

• Lookup Value is the value to look for in Table, e.g. A2

• Table is an array of selected columns to be looked up for your 'Lookup Value'. Be aware to add here additional columns related to 'Lookup Value' which will be used as replace values.

  E.g. if you looking for ID and want to replace it with name, select in 'lookup sheet' the column named 'ID' and the column named 'Name'. The table formula would be:

  =NameofSheet!ColumnNameOfID:ColumnNameOfName
  

• Column is a column name from Table to be displayed instead of 'Lookup Value'

Insert a column next to your foreign key column and a column next to your foreign key 2 column and use a vlookup to reference your second table (in the screenshot below the second table begins at column I)

The standard syntax looks something like this:

The lookup value is your key,foreign key1 for your first column and foregin key2 for your second. The table is your search range,your second table which in the below screenshot is I to E, your column is the index of your search range you would like to retrieve.

The last value "Approximate match" is a flag that indicates whether or not your search key will match your criteria exactly. For the purposes you have outlined above, this should be set to false.

=VLOOKUP(C2,$I$2:$J$12,2,FALSE)

Drag your formula down the new column. Copy the values over the foreign key columns. Make sure you copy and paste values, otherwise your vlookup formula will be pasted in.

The $ in front of the "I" and "2" indicate that the range will remain stationary even when dragged into other cells, which is not ideal if you have a table with dynamic data. Alternatively you could remove the row restrictions on the range and use $I:$J as your range.

If you have any trouble please let me know and I will help you out as best I can. You can find more info on how to use vlookup here.