VLOOKUP Errors

[jdkuehne]

I'm working in Excel 2016 and VLOOKUP is kicking my butt!

My workbook has two sheets, "Form" and "Table". On "Form", I've created a form with a drop-down list from a named range ("Range") on a table in "Table". The named table ("Data") has four columns. Column 1 (labeled "Item") contains the named range "Range". Columns two - four  in "Data" are the data items that I want to include in adjacent columns on "Form" when an item is selected from the drop-down list.

The "definitive" guide I was following indicates the syntax should be as follows:

=VLOOKUP([@Item],Data,2,FALSE)

This results in the "The syntax of this name isn't correct" error and [@Item] is highlighted in the editor window. If I remove the @, as some comments on the guide suggested,

=VLOOKUP([Item],Data,2,FALSE)

I get the "There's a problem with this formula" error. If I then remove the square brackets (no other tutorial used them),

=VLOOKUP(Item,Data,2,FALSE)

I get the #N/A error. If I substitute the named range for the column label

=VLOOKUP(Range,Data,2,FALSE)

I get data back, but it's the last row in column 2 of the table, and has no relation to the drop-down list. The scope of the named Table and named range are both Workbook, if this helps.

Numerous other variations, including adding the worksheet name, have resulted in #NAME?,#N/A, and #VALUE, but I can't figure out where I'm going wrong. I've read several tutorials on this function, and none have enlightened me. Please help!

[#2]

@jdkuehne 

You don't mention exactly what column or cell this VLOOKUP formula is in. And where that is in relation to the cell containing the dropdown selection that is to be used as the first reference in the VLOOKUP. Those are two key elements.

• Assuming for sake of illustration that the drop down is in cell C2 of the "Form" tab, I think your VLOOKUP should read simply
• =VLOOKUP(C2,Data,2,FALSE)
• The drop down may itself refer to the list of "Items" but the VLOOKUP should refer to the actual item that was selected.

That's my suggestion based on what I've gleaned from what you've written. If that doesn't make sense, or doesn't work, might I suggest you post a copy of the spreadsheet on OneDrive or GoogleDrive, followed by posting a link here granting access to look at your workbook.

[#3 · Accepted solution]

That absolutely is the correct answer. I was being led astray by different tutorials not explaining things clearly. Thank you so much!

[#4]

The structured references seem to be the issue. I recommend building the VLOOKUP by selecting your lookup_value and table array by hand. Excel will put in the table references for you where needed.