VLOOKUP returning "incorrect argument set" error

Wesley Knapp

BRAND NEW TO SMARTSHEETS HERE:

I have two sheets which have a unique ID column in common as a key. Sheet 1 will have only a single time this key will be listed. Sheet 2 could have many entries that have the same ID value displayed multiple times.

I am trying to pull a field from sheet 1 into sheet 2. I want column 6 from sheet 1 to display in sheet 2 wherever the keys match.

I am using the following code.

=VLOOKUP([External Audit Reference Number]@row, {External Audit - Data Range 1}, 6, false)

Help please :)

Paul Newcome

Ok. The issue lies in step 2. You are only selecting one column for the range, but you are telling it to pull from column 6. Since there are not 6 columns within the range of the cross sheet reference, it has no idea what you are talking about. The range needs to include columns 1 through 6 on the reference sheet.

Edit your range and click on the column header for column 1. Then hold down shift and click again on the header for column 6. Making that your range should solve the issue.

Paul Newcome

Which column(s) in Sheet 1 are you selecting when you create your cross sheet reference?

Wesley Knapp

Let me summarize the steps I took to make the formula:

1 - I type in "=Vlookup(" and then I select column 1 on the sheet that i'm in (which is Sheet 1).  This is the data field I want to match on sheet 2, and display column 6 from sheet 2.

2- then I add a comma so i'm in the "lookup_table" portion of the formula and I click "reference another sheet" link.  I go to sheet 2 and select all of column 1 (which contains the "key" data) by clicking on the header of the column. 

3- then I add a comma and input "6", because I want column 6 to display in sheet 1.

4 - then I add the comma and "false" because I want an exact match, and closing parenthesis, so I get this:

=VLOOKUP([External Audit Reference Number]@row, {External Audit - Data Range 1}, 6, false)

With the result:   #incorrect argument set

Paul Newcome

Ok. The issue lies in step 2. You are only selecting one column for the range, but you are telling it to pull from column 6. Since there are not 6 columns within the range of the cross sheet reference, it has no idea what you are talking about. The range needs to include columns 1 through 6 on the reference sheet.

Edit your range and click on the column header for column 1. Then hold down shift and click again on the header for column 6. Making that your range should solve the issue.

Wesley Knapp

That worked - thank you. I thought I was defining where to find the matching field, not all of the data.

Is there any advantage to not selecting the entire sheet when identifying your table lookup? I'm just wondering why the default isn't the entire sheet, if you are specifying which columns to look at anyway.

Paul Newcome

The system isn't registering exactly which function is being used by the cross sheet reference. Some cross sheet references only require a single column or a single row, or even a single cell or possibly group of cells.

The advantages to not selecting the entire sheet is that you are less likely to run into various limits regarding referenced cells.