Vlookup referring to table data in a different sheet
excel, vlookup
Solution
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
Problem
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) ```