Vlookup referring to table data in a different sheet

Question

I would like to use a VLOOKUP function referring to a data table placed in a different sheet from the one where the VLOOKUP function in written.

Example: in Sheet 1, cell AA3 I would like to insert the VLOOKUP function. I want the function to check the number in cell M3, find the same number in Sheet 2 range address A2:Q47 first column, and reproduce the value in the 13th column of that table.

I've written this function but it reports #N/A as a result:

=VLOOKUP(M3,Sheet1!$A$2:$Q$47,13,FALSE)

Answer 1

One of the common problems with VLOOKUP is "data mismatch" where #N/A is returned because a numeric lookup value doesn't match a text-formatted value in the VLOOKUP table (or vice versa)

Does either of these versions work?

=VLOOKUP(M3&"",Sheet1!$A$2:$Q$47,13,FALSE)

or

=VLOOKUP(M3+0,Sheet1!$A$2:$Q$47,13,FALSE)

The former converts a numeric lookup value to text (assuming that lookup table 1st column contains numbers formatted as text). The latter does the reverse, changing a text-formatted lookup value to a number.

Depending on which one works (assuming one does) then you may want to permanently change the format of your data so that the standard VLOOKUP will work