Excel vlookup返回不可用 [英] Excel vlookup return not available

问题描述

我想使用vlookup填写名称列，这是我的交易表

这是我的主文件

是的，它们是相同的数字，但是为什么我的vlookup不根据查找的值返回相应的名称?

vlookup是否符合数据类型?例如文字，数字还是一般?

我一遍又一遍地更改了数据类型，并返回了相同的不可用"

我的excel 2007有什么问题吗?

解决方案

问题是 VLOOKUP 在第一列(即A列)中查找电话号码.，则需要使用 INDEX 和 MATCH :

  = INDEX(电话簿！$ A $ 2:$ A $ 45，MATCH(B2，电话簿！$ B $ 2:$ B $ 45,0)) 

INDEX 如下:

  = INDEX(范围，行号，[列号]) 

它将返回行号 Row Number 和列 Column Number 上的 Range 范围内的单元格值.我将 Column Number 放在方括号之间，因为它是可选的(例如，如果范围在A:A之内，则只有1列)

要获取行号，可以像上面一样使用 MATCH .它的工作原理类似于 VLOOKUP ，但是它不返回匹配单元格的值，而是返回匹配单元格的行号(如果在水平范围内使用，则返回列号).

MATCH(B2，Phonebook！$ B $ 2:$ B $ 45,0)因此在 B2:B45 范围内查找 B2 工作表电话簿( 0 表示完全匹配)并给出行号.

然后，您告诉Excel从 Phonebook！$ A $ 2:$ A $ 45 范围返回单元格的值，并从 MATCH 获得行号.

>

i want to fill the name column using vlookup, here is my transaction table

and here is my master file

yes, they're the same number, but why do my vlookup doesn't return the corresponding name based on looked up value ?

does vlookup comply with data type ? like text, or number, or general ?

i have changing the data type, over and over, and return the same "Not Available"

is there anything wrong with my excel 2007 ?

解决方案

What's wrong is that VLOOKUP is looking for the phone number in the first column, meaning in column A. For 'backwards lookup', you will need to use INDEX and MATCH:

=INDEX(Phonebook!$A$2:$A$45,MATCH(B2,Phonebook!$B$2:$B$45,0))

INDEX is as follows:

=INDEX(Range, Row Number, [Column Number])

It will return the value a cell from the range Range that is on the row number Row Number and column Column Number. I have put Column Number between square brackets because it is optional (you have only 1 column if you have a range that is within A:A for example)

To get the row number, you can use MATCH like the above. It works a bit like VLOOKUP, but instead of returning the value of the matching cell, it returns the row number of the matching cell (or column number if you use it on a horizontal range).

MATCH(B2,Phonebook!$B$2:$B$45,0) thus looks for B2 in the range B2:B45 of the worksheet Phonebook (0 stands for exact match) and gives the row number.

Then, you tell Excel to return the value of the cell from the range Phonebook!$A$2:$A$45 and row number obtained from MATCH.

这篇关于Excel vlookup返回不可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！