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)

score 21 · Answer 1 · answered Feb 20 '13 at 19:10

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

+1 for the neat trick for coercing the data type in the lookup value from text to number, or vice versa! — chuff, Feb 26 '13 at 05:30

score 4 · Answer 2 · answered May 06 '16 at 08:38

4

I faced this problem and when i started searching the important point i found is, the value u are looking up i.e M3 column should be present in the first column of the table u want to search https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 check in lookup_value

answered May 06 '16 at 08:38

Vishruth

41
1

This fixed it for me! Barry Houdini's (top answer) didn't do the trick. – WebMW Sep 23 '16 at 04:41
this info was amazing...this should be the top answer – Nitin Mar 15 '18 at 20:43

score 0 · Answer 3 · answered Feb 20 '13 at 18:18

0

Your formula looks fine. Maybe the value you are looking for is not in the first column of the second table?

If the second sheet is in another workbook, you need to add a Workbook reference to your formula:

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

answered Feb 20 '13 at 18:18

chuff

5,846
1
21
26

Thank you for take the time to answer. The value was in the first column of the second table. – Gianluca Feb 25 '13 at 16:26

score 0 · Answer 4 · answered Jan 08 '14 at 12:15

0

There might be something wrong with your formula if you are looking from another sheet maybe you have to change Sheet1 to Sheet2 ---> =VLOOKUP(M3,Sheet2!$A$2:$Q$47,13,FALSE) --- Where Sheet2 is your table array

answered Jan 08 '14 at 12:15

Jong

1

score 0 · Answer 5 · answered Mar 26 '14 at 13:52

0

This lookup only features exact matches. If you have an extra space in one of the columns or something similar it will not recognize it.

answered Mar 26 '14 at 13:52

Colin

1

score 0 · Answer 6 · edited Dec 14 '15 at 09:20

0

I have faced similar problem and it was returning #N/A. That means matching data is present but you might having extra space in the M3 column record, that may prevent it from getting exact value. Because you have set last parameter as FALSE, it is looking for "exact match". This formula is correct: =VLOOKUP(M3,Sheet1!$A$2:$Q$47,13,FALSE)

edited Dec 14 '15 at 09:20

Yaron Schwimmer

5,327
5
36
59

answered Dec 14 '15 at 08:42

Gurmeet singh

1
1

score -1 · Answer 7 · edited Sep 01 '15 at 20:49

-1

Copy =VLOOKUP(M3,A$2:Q$47,13,FALSE) to other sheets, then search for ! replace by !$, search for : replace by :$ one time for all sheets

edited Sep 01 '15 at 20:49

WOUNDEDStevenJones

5,150
6
41
53

answered Sep 01 '15 at 19:25

Hussein Mohammed

1

Vlookup referring to table data in a different sheet

7 Answers7

Linked