擅长讲解-例子的怎样使用VLOOKUP作用

张贴在2007年9月5日在1:50 pm

保证读其他岗位从擅长讲解系列!

这速成为使用需要帮助的那些人 VLOOKUP 作用擅长。 VLOOKUP是一个非常有用的作用为容易搜寻通过一个或更多专栏在发现相关数据的大活页练习题。您能使用HLOOKUP做同一件事为一个或更多列数据。基本上当使用VLOOKUP时，您在这个其他数据设置要求“在价值，这里发现那价值，然后退回到我另一个专栏的价值同样数据设置”。

如此您也许问怎么这可以是有用的？很好，例如作为，以下样品报表我为本指南创造了。报表是非常简单的：一板料有信息关于两三个车主例如汽车的名字、id，颜色和马力。第二板料有汽车和他们的实际式样名字的id。共同的数据项在二板料之间是汽车id.

现在，如果我在板料1想显示汽车的名字，我在第二板料可以使用VLOOKUP到查寻每价值在车主板料，发现那价值，然后退回第二个专栏(汽车模型)作为我的期望值。如此怎么您是否对此去？涌出您首先将需要输入惯例细胞 H4. 注意我已经输入充分的惯例细胞 F4 通过 F9. 我们通过什么将走每个参量在那个惯例实际上意味。

这什么惯例看似完全：

=VLOOKUP (B4， Sheet2! $A ：$B， 2，错误)

有5份到这个作用：

1. =VLOOKUP - =表示这个细胞在是搜寻的VLOOKUP作用通过一个或更多数据列的我们的情况将包含一个作用和。

2. B4 -第一个论据为作用。 This is the actual search term that we want look for. The search word or value is whatever is entered into cell B4.

3. Sheet2!$A:$B - The range of cells on Sheet2 that we want to search through to find our search value in B4. Since the range resides on Sheet2, we need to precede the range with the name of the sheet followed by an !. If the data is on the same sheet, there is no need for the prefix. You can also use named ranges here if you like.

4. 2 - This number specifies the column in the defined range that you want to return the value for. So in our example, on Sheet2, we want to return the value of Column B or the car name, once a match is found in Column A. Note, however, that the column’s position in the Excel worksheet does not matter. So if you move the data in Columns A and B to D and E, let’s say, as long as you defined your range in argument 3 as $D:$E, the column number to return would still be 2. It’s the relative position rather than the absolute column number.

5. False - False means that Excel will only return a value for an exact match. If you set it to True, Excel will look for the closest match. If it’s set to False and Excel cannot find an exact match, it will return #N/A.

Hopefully, you can now see how this function can be of use, especially if you have lots of data exported out from a normalized database. There may be a main record that has values stored in lookup or reference sheets. You can pull in other data by “joining” the data using VLOOKUP.

Another thing you may have noticed is the use of the $ symbol in front of the column letter and row number. The $ symbol tells Excel that when the formula is dragged down to other cells, that the reference should remain the same. For example, if you were to copy the formula in cell F4 to H4, remove the $ symbols and then drag the formula down to H9, you’ll notice that the last 4 values become #N/A.

The reason for this is because when you drag the formula down, the range changes according to the value for that cell. So as you can see in the picture above, the lookup range for cell H7 is Sheet2!A5:B8. It simply kept adding 1 to the row numbers. To keep that range fixed, you need to add the $ symbol before the column letter and row number.

One note: if you’re going to set the last argument to True, you need to make sure that the data in your lookup range (the second sheet in our example) is sorted in ascending order otherwise it will not work!

Any questions, post a comment!

Technorati Tags: vlookup, vlookup function, how to use vlookup, vlookup help, vlookup examples, vlookup tutorial