|re: To hopefully clarify, the column index number is what needed to be changed. |
In your example the first argument for the K column was looking in column index 2 (traffic) instead of 1 (keyword).
bzzzzzt! Wrong answer!
Did you read the Help files on the VLOOKUP function as I suggested?
Here is the syntax of the VLOOKUP function directly from the Excel Help files:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Here is the description of the table_array argument, my emphasis added:
table_array Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are the values searched by lookup_value.
In other words the VLOOKUP function always looks in the first column of the table_array which in your case is $A$2:$B$501. You can not change that.
Now here is the the description of the col_index_num argument:
col_index_num Required. The column number in the table_array argument from which the matching value must be returned.
Putting those 2 descriptions together, here is how the formula works:
The VLOOKUP will always look in the first column of the table_array (e.g. Column A if the table_array is $A2:B501, Column F if the table_array is $F$1:$Z$50) and return the value from the column number specified by the col_index_num argument.
Since your table_array is only 2 columns wide, your col_index_num can only be 1 or 2. In your case, if it's 1, it will return the value from Column A. If it's 2 it will return the value from Column B. If your table_array was $F$1:$Z$50, a col_index_num of 1 would refer to Column F, a col_index_num of 5 would refer to Column J.
The col_index_number has nothing to do with where the VLOOKUP function searches for the lookup_value.
will make absoutey no difference because the ISNA is only concerned with whether or not the lookup_value is found in the first column of the lookup_table. The ISNA function doesn't care about or even know about the col_index_number.
If you want to proof that to yourself, use any col_index_num value you want for the first part of the formula (the VLOOKUP within the ISNA function), e.g. 45, and the formula will still work, even if there is no Column 45 in your table_array. Use 45 in the second part and the formula will fail.
Now that you have everything working, try this formula and you'll see what I mean.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.