HOWTO – Gather and present data from multiple lookup tables in Microsoft Excel using VLOOKUP
Ever been asked to extract data from different sources (excel sheets preferably) into one table/sheet in Excel?
I have.
Seen to many copy paste approaches, I finally looked for a better way myself. As long as you have one value that are unique per row and shared between the data sources , you can solve this problem with some Excel magic. Let’s take a look at VLOOKUP.
Learning By Example:
Sorry for the lame example data, no imagination at the moment, sorry ;)
Source A:
| PersonId | Shoe Size | Age |
| 1 | 36 | 15 |
| 2 | 42 | 26 |
| 3 | 33 | 14 |
| 4 | 45 | 23 |
| 5 | 39 | 23 |
| 6 | 38 | 27 |
| 7 | 38 | 43 |
| 8 | 37 | 38 |
| 9 | 43 | 18 |
| 10 | 38 | 16 |
| 11 | 40 | 17 |
| 12 | 27 | 5 |
Source B
| Person Id | Favourite Animal |
| 1 | Ape |
| 23 | Lion |
| 2 | Cobra |
| 25 | Lion |
| 4 | Fish |
| 14 | Ape |
| 3 | Lion |
| 15 | Cobra |
| 6 | Lion |
| 16 | Fish |
| 7 | Ape |
| 17 | Lion |
| 9 | Cobra |
| 18 | Lion |
| 8 | Fish |
| 77 | Ape |
| 10 | Lion |
| 9 | Cobra |
| 12 | Lion |
| 5 | Fish |
| 34 | Ape |
| 11 | Lion |
| 56 | Cobra |
| 12 | Lion |
You would like a third table with all values per person, let’s add another column to Source A, using VLOOKUP.
We are looking for matched row in Source B, using the shared key “Person Id”.
Syntax Break Down
Or in English: Use the value in cell A4 and look for a exact match in the range G4:H27, if found, use the value in the second column.
Important! Since we can not specify which column the key is located in in the lookup range, it has to be in the first column. (In this case the G column).
What’s about all those $ in the formula?
A $ before a column or range reference in a formula makes it static, so when we drag or copy the formula those values remains the same. In this case it is very important that all rows looks in the full range, and then we add $ to the range. G4:H27 becomes $G$4:$H$27. Both the column and rows are locked, and we can drag the formula down and to columns to the right. And it will work.
If not: =VLOOKUP(A4;G4:H27;2;FALSE) will be =VLOOKUP(A5;G5:H28;2;FALSE) on the next row.
We lost the first value, we do need the $. Or you can update each rows formula manually….. guess not.
Have fun!!
Comments