Vlookup引用不同工作表中的表数据 [英] Vlookup referring to table data in a different sheet
问题描述
我想使用VLOOKUP函数，该函数引用的数据表与编写VLOOKUP函数的表位于不同的表中.
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.
示例:在工作表1中，AA3单元格我想插入VLOOKUP函数.
我希望函数检查单元格M3中的数字，在工作表2范围地址A2:Q47的第一列中找到相同的数字，并在该表的第13列中重现该值.
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.
我已经编写了此函数，但结果报告为#N/A:
I've written this function but it reports #N/A as a result:
=VLOOKUP(M3,Sheet1!$A$2:$Q$47,13,FALSE)
推荐答案
VLOOKUP的常见问题之一是数据不匹配"，其中由于数字查找值与文本格式不匹配而返回了#N/A VLOOKUP表中的值(反之亦然)
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)
或
=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.
根据哪种方法起作用(假设一种方法起作用)，则您可能希望永久更改数据格式，以便标准的VLOOKUP起作用
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
这篇关于Vlookup引用不同工作表中的表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
