Hello; I am having trouble again, HELP!

I am trying to use the =VLOOKUP function and I keep getting an error for the [RANGE_LOOKUP] area. I have chosen "FALSE" for this.

This is what my attempt is, to use the currency value in cell C17 and find an exact match in an array from cell N6:V120074 finding the matched value in cell N11831 and returning the value found in column P or number 16 to cell C18.

Here is my formula







Your formula =VLOOKUP(C17,N6:V120074,16[FALSE]) contains the parameters C17, N6:V120074, 16 and FALSE. They must be listed separated by commas, thus, =VLOOKUP(C17,N6:V120074,16,FALSE). Square brackets are part of a syntax not applicable here and, anyway, would be used as an alternative means of specifying a range. The range here is N6:V120074.

Without the syntax error, your formula expresses the following.

  1. Look for the first occurance of C17 in the first column of the range N6:V120074. Note that the first column of the specified range is column N.
  2. When it is found return the value in the 16th column of the specified range from the row where the match was found. Of course, range N6:V120074 only has 9 columns, N:V. Therefore a lookup of the non-existent 16th column is likely to cause an error.

Note that, since you wish to return a value from column P, you need to specify a range of only 3 columns, N6:P120074. The first column is always the column in which to look for the match, and the column from which yo return the value (column P) is always column 3 while the first is N, regardless of how many further columns there might be in the specified range, as Ken has pointed out in his answer before me.

Note that the FALSE argument answers the question of "would you like to accept an approximate match?" While you want an exact match the answer is no, expressed as FALSE. If it were TRUE the formula would match the first value which is not smaller than C17. This only makes sense if the data are sorted in ascending order. Unfortunately, TRUE is the default and is applied if the argument is omitted, which is possible, like in =VLOOKUP(C17,N6:V120074,16). Therefore the FALSE is very important to remember.


Answer the Question

You must create an account to use the forum. Create an Account or Login