Vlookup referring to table data in a different sheet

excel vlookup
Gianluca picture Gianluca · Feb 20, 2013 · Viewed 151.8k times · Source

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

barry houdini picture barry houdini · Feb 20, 2013

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

Related questions

Remove #N/A in vlookup result

How do I modify this function so that the result will merely be a blank cell rather than having #N/A show up if B2 has nothing in that cell? I think I might need something like an ISERROR check …

Read More
Excel - find cell with same value in another worksheet and enter the value to the left of it

I have a report that is generated in Excel which contains an employee's number, but not his/her name. Not every employee will be on this worksheet on any given day. In a 2nd worksheet I have a list of …

Read More
Comparing two columns, and returning a specific adjacent cell in Excel

I am using a combination of if, vlookup, match, iserror functions, and unfortunately I've not been able to find the right formula. Comparing two columns for matches is easy enough. the tough part has been returning a specific cell once …

Read More