Excel: VLOOKUP matching one column to another sheet and joining the data

Question

I'm running into issues with VLOOKUP formula. I've been looking at tutorials and even followed another example explaining on StackOverflow but the cells still say #N/A.

This is my formula.

=VLOOKUP($L2,surveydata,M$2,FALSE)

I'm trying to match the values in column L (phone) on one sheet with another sheet that contains phone as well (and labeled all the data as 'surveydata'), and then pull all the columns after into the original sheet.

== Table A ==        == surveydata ==     == Table result ==
Column L             Column A, B, C       Column L, M, N
Phone                Phone,Fname,Lname    Phone,Fname,Lname
012                  789,John,Smith       123
789                  012,Stan,Smith       456,Jane,Smith
456                                       789,John,Smith
123                  456,Jane,Smith       012,Stan,Smith

Am I missing something entirely?

Phylyp Phylyp · Accepted Answer · 2017-02-11T02:38:25

The third argument of the VLOOKUP function is the position of the column whose data you want to return. You've used a cell reference $M2 which won't work (unless column M is hard-coded with the value 2).

So, assuming the 'surveydata' named range refers to your lookup table, then in column M you'll enter the formula:

=VLOOKUP($L2, surveydata, 2, FALSE)

In column N you'll enter:

=VLOOKUP($L2, surveydata, 3, FALSE)

If VLOOKUP cannot find the value in the lookup table, it returns #N/A. You might want to use a combination of the IF() function and ISNA() functions to detect #N/A cases and display a more graceful error, message, or just an empty string.

Also, take a look at this page from Microsoft which explains how the INDEX() and MATCH() functions can be used as an alternative to VLOOKUP() - the difference is that you can specify the column you want returned, instead of the column's position.

Excel: VLOOKUP matching one column to another sheet and joining the data

1 Answers