I had an email from Bobcat today asking how to lookup data that is spread across multiple columns.
Table 1 has data with the name in just one column:
And Table 2 has the name across 3 columns (D, E and F):
We want to find the Employee number from Table 1 and put it in column G of Table 2.
The solution is quite simple. We just need to join together the cells containing the name in Table 2, before looking them up in Table 1.
We can do this within a VLOOKUP formula like this:
=VLOOKUP(D6&” “&E6&” “&F6,table_1,2,FALSE)
Note: table_1 in the above formula is the named range for cells A6:B11
See in the formula bar how I’ve joined the text from columns D, E and F together using the ampersand symbol. I’ve also added a space between the text by inserting double quotes with a space between.
The VLOOKUP formula is resolving the D6&” “&E6&” “&F6 arguments of the formula like this:
=VLOOKUP(William J Oxley,table_1,2,FALSE)
Thanks for your question Bobcat.
