VLookup formula contains reference error:

Hi cslviola,

I see two issues here.

You wrote:

"N39 has a SUM formula that adds up the rubric points

Table 2 column A has the rubric grades

Table 2 column B has the converted grades

I put FALSE, as I need an exact match"

Does your lookup table include every possible value of the sum of the rubric grades?

A "close match" in these functions means "the largest value that is less than or equal to the search value."

If every possible search value is included in column a of the lookup table, then a close match will alway be a match to the value that is equal to the search value (ie. an exact match).

If there are possible rubric point sums that are not included in the table, then a function requiring an exact match will return an error if he search vaue (the rubric point sum) is one of the values mising from column A of the Lookup table. Unless that is your expected result, you do not need (and do not want) an exact match.

Regarding your formula:

I see some confusion here between the syntax requirements of LOOKUP and those of VLOOKUP.

=VLOOKUP(N39,Table 2 :: A1:A51,Table 2 :: B1:B51,FALSE)

Here's the LOOKUP syntax:

LOOKUP(search-for, search-where, result-values)

As a LOOKUP formula, your formula above would be:

=LOOKUP(N39,Table 2::A1:A51,Table 2::B1:B51)

LOOKUP always requires only a close match, which is the better choice for your issue.

The syntax for VLOOKUP is a bit different:

VLOOKUP(search-for, columns-range, return-column, close-match)

search-for is the same as for LOOKUP, but the next argument is different.

columns range must specify all of the columns and rows making up the lookup table. In your case, that's columns A and B of Table 2. The search column is always the leftmost column of the Lookup table.

return column is specified as the number corresponding to the position if the return column in the Lookup table. For your case, the return column is column 2 (NOT because it is column B, but because it is the second column of the Lookup table. If your Lookup table consisted of cells J1:K51, the return column (K) would still be column 2)

close value, if set as FALSE, requires an exact match of the search value and the value it finds in the search column; set to TRUE, or omitted from the formula means a close match (as defined above) is aceptable.

Your formula as a VLOOKUP formula, would look like this:

=VLOOKUP(N39,Table 2::A1:B51,2,FALSE) (requiring an exact match)

OR

=VLOOKUP(N39,Table 2::A1:B51,2) (requiring a close match, as defined above)

Personally, I'd go for the LOOKUP version here.

Regards,

Barry

Does Table 2 have cells B1 through B51?

if not, then the reference is, indeed, invalid and should be corrected to match the number of rows in the table. If the only thing in Table 2 is the grade translation then I think it would be safe to change the reference to:

=VLOOKUP(N39,Table 2 :: A1:A51,Table 2 :: B,FALSE)

which is the whole column B.

If this doesn't help please post back a screen shot