Excel Vlookup producing #N/A error when column doe

Question

I have two sheets in a workbook with data pulls from different days to add a new column to the data needed. However, I need to work only with the original data pull (as new items have been added since original pull and those can be ignored). So sheet 1 has 53,875 line items and sheet 2 has 54134 items.

The first column is a user ID, so I have sorted both sheets on that column.

What I would like to do, is take the data from column L on sheet 2, and insert it into a blank column on sheet 1 for Sheet1!Ax = Sheet2!Ax. The formula that I came up with is:

=VLOOKUP(Sheet1!A1,Sheet2!A1:L1,12,0)

I then filled it in for the rest of the blank cells in the first sheet. It worked fine, until the first new data item in Sheet 2 failed to match Sheet 1. After that, every cell has the #N/A error.

Is Vlookup the correct tool to use for this task, or would it be something else?

Answer 1

If you can, use tables instead of ranges - a table will automatically expand with new data, an absolute range won't.

Click anywhere in your "source" range, select "Format as Table" from the Home Ribbon (under the "Styles" group). Then do the same for your "target" range.

Now that you have two tables, you can use table formulas and never need to worry about "dragging formulas down" ever again, because the formula is automatically applied to the entire column!

Say you have a Key column in Table1 and Table2, and you're looking for the corresponding Column1 value in Table2 for each Key in Table1; the formula could look like this:

=VLOOKUP([@Key],Table2[[#All],[Key]:[Column1]],2,FALSE)

If headings change, the formula updates automatically. If data is added in Table1, the lookup formula is automatically added to the new rows. If data is added in Table2, the formula doesn't need to be adjusted!

Love tables, they'll love you back!

Answer 2

You can also use :

=INDEX(Sheet2!L$1:L$54134,MATCH(Sheet1!$A1,Sheet2!A$1:A$54134,0))

Not tested but should work and I tend to find is more stable and possibly quicker than vlookup.