函数 VLOOKUP 的 Excel 问题 [英] Excel issue with Function VLOOKUP
问题描述
我知道这可能不是问一个纯粹的 excel 问题的最佳场所，但是.我有一个带有以下标题的表格:数字、日期、名称.在另一个文件中，我需要将数字与名称联系起来.它并不总是以相同的顺序，因此是 VLOOKUP 公式.这是下面的数据.
1 15-Nov Allen 15-Nov10 11 月 15 日艾伦 11 月 15 日19 11 月 15 日艾伦 11 月 15 日6 3 月 5 日 阿瓦隆 5 月15 3 月 5 日 阿瓦隆 5 月24 3 月 5 日 阿瓦隆 5 月9 11 月 15 日 伯格 11 月 15 日18 11 月 15 日 伯格 11 月 15 日27 11 月 15 日 伯格 11 月 15 日8 1-Sep Fischer 1-Sep17 1-Sep Fischer 1-Sep26 1-Sep Fischer 1-Sep3 14-Oct Kiser 14-Oct12 14-Oct Kiser 14-Oct21 14-Oct Kiser 14-Oct2 15-Oct Remmert 15-Oct11 15-Oct Remmert 15-Oct20 15-Oct Remmert 15-Oct4 1-Sep Roos 1-Sep13 1-Sep Roos 1-Sep22 1-Sep Roos 1-Sep7 2 月 6 日 托莫 2 月 6 日16 2 月 6 日 托莫 2 月 6 日25 2 月 6 日 托莫 2 月 6 日5 1 月 1 日 洗 1 月 1 日14 1 月 1 日 洗 1 月 1 日23 1 月 1 日 洗 1 月 1 日所以让我们假设它的标题是 A、B、C、D.所以在新文件中，为了从我使用的公式中的数字中获取名称:
=VLOOKUP(A1,[Workbook1]Sheet1!$A$1:$D$27,3,FALSE)Workbook1 和 Sheet1 是数据所在的位置.接下来，我需要通过将文件链接到名称来从文件中获取日期.我知道我可以将它与数字联系起来，但这是工作的事情，它们的排列方式从最旧到最新的日期不同，不像这个一样.在我看来，excel 中的 Date 函数非常愚蠢.所以我用了公式
=VLOOKUP(B1,[Workbook1]Sheet1!$A$1:$D$27,1,FALSE)
并通过将 1 替换为 4 对其进行迭代.它不断返回 N/A.即使我将 Date 的格式更改为 General 它仍然没有返回任何内容.我不明白我在这里做错了什么.
VLOOKUP 语法:
<块引用>/ 与 是用于在值用完时显示空字符串(例如")而不是错误消息(如上例图像中的 C4 和 D4).
总而言之，使用 VLOOKUP 函数 返回所求值右侧的值；使用 INDEX/MATCH 函数对从匹配列的任一侧检索值.
I know this might not be the best place to ask a purely excel question, but. I have a table with the following headers: Number, Date, Name. In another file I need to link the number with the name. It is not always in the same order, hence the VLOOKUP formula. Here is the data below.
1 15-Nov Allen 15-Nov
10 15-Nov Allen 15-Nov
19 15-Nov Allen 15-Nov
6 5-Mar Avalon 5-Mar
15 5-Mar Avalon 5-Mar
24 5-Mar Avalon 5-Mar
9 15-Nov Burg 15-Nov
18 15-Nov Burg 15-Nov
27 15-Nov Burg 15-Nov
8 1-Sep Fischer 1-Sep
17 1-Sep Fischer 1-Sep
26 1-Sep Fischer 1-Sep
3 14-Oct Kiser 14-Oct
12 14-Oct Kiser 14-Oct
21 14-Oct Kiser 14-Oct
2 15-Oct Remmert 15-Oct
11 15-Oct Remmert 15-Oct
20 15-Oct Remmert 15-Oct
4 1-Sep Roos 1-Sep
13 1-Sep Roos 1-Sep
22 1-Sep Roos 1-Sep
7 6-Feb Tomo 6-Feb
16 6-Feb Tomo 6-Feb
25 6-Feb Tomo 6-Feb
5 1-Jan Wash 1-Jan
14 1-Jan Wash 1-Jan
23 1-Jan Wash 1-Jan
So Lets assume it is titled as Column A, B, C, D. So in the new file in order to grab the name from numbers I used the formula:
=VLOOKUP(A1,[Workbook1]Sheet1!$A$1:$D$27,3,FALSE)
Workbook1 and Sheet1 is where the data is. Next I need to grab the date from the file by linking it to the name. I know I can link it to the number but it is a thing for work and the way they are arranged are with Different dates from oldest to newest, not the same like this one. The Date function in excel is extremely stupid in my opinion. So I used the formula
=VLOOKUP(B1,[Workbook1]Sheet1!$A$1:$D$27,1,FALSE)
and iterations of it by replacing the 1 with 4. It keeps returning N/A. Even when I change the formatting of the Date to General it still returns nothing. I dont get what I am doing wrong here.
VLOOKUP syntax:
VLOOKUP(lookup_value , table_array, col_index_num, (optional) [range_lookup])
The VLOOKUP function will always look up the lookup_value in the first column of the table_array. It then attempts to return the value in the col_index_num of the corresponding row in the table_array. The range_lookup tells VLOOKUP whether to look for an exact or an approximate match. In your case, you will be looking for an exact match.
In no way can VLOOKUP return a value in the table_array that is to the left of the column looking to match the lookup_value. The lookup_value is always looked for in the first column and VLOOKUP returns a value in a column to the right.
Your formula to look up the name based upon the ID number in column A works well for this.
=VLOOKUP(A1, [Workbook1]Sheet1!$A$1:$D$27, 3, FALSE)
With 8 in A1, the above formula returns Fischer in B1.
When you are looking to return the dates, VLOOKUP will work for the one to the right of the name in Sheet1's column D but it cannot the return the date from Sheet1's column B basing the lookup_value on the name. You will need an INDEX/MATCH function pair for that. In addition, retrieving the second and third date sets that match the name is easier with INDEX/MATCH than VLOOKUP.
INDEX and MATCH syntax:
INDEX(array, row_num, column_num)
MATCH(lookup_value, lookup_array, (optional) [match_type])
If you are looking to return the date from Sheet1's column B using the name just retrieved from Sheet1's column C, you will have to use an INDEX/MATCH pair. Since we are using this for one date, it is eaay to use it for the other although strictly speaking, VLOOKUP could be used for this.
Use these two formulas in C1 and D1.
=INDEX([Workbook1]Sheet1!B:B, MATCH(B1, [Workbook1]Sheet1!C:C, 0))
=INDEX([Workbook1]Sheet1!D:D, MATCH(B1, [Workbook1]Sheet1!C:C, 0))
Format the cells for a custom number format of d-mmm. Your results should be similar to the following.
If you wanted to return the second and third dates from Sheet1 that match the name in column B, change the target worksheet's C1 and D1 to to these formulas that use the SMALL function togewther with the ROW function and COUNTIF function to produce the additional dates.
Make a minor tweak to the formula in B1 so it can be filled down then supply the next two formulas for C1 and D1.
=VLOOKUP(A$1, [Workbook1]Sheet1!$A$1:$D$27, 3, FALSE)
=IFERROR(INDEX([Workbook1]Sheet1!B$1:B$999, SMALL(INDEX(ROW($1:$999)+([Workbook1]Sheet1!C$1:C$999<>B$1)*1E+99, , ), COUNTIF(B$1:B1, B1))), "")
=IFERROR(INDEX([Workbook1]Sheet1!B$1:B$999, SMALL(INDEX(ROW($1:$999)+([Workbook1]Sheet1!C$1:C$999<>B$1)*1E+99, , ), COUNTIF(B$1:B1, B1))), "")
Fill B1:D1 down down to catch all of the possible dates from Sheet1 that match the name returned from your original ID lookup. Your results should look like,
You may notice that I sequenced the identical dates on Sheet1 in order to demonstrate the method. The IFERROR function is used to display an empty string (e.g. "") instead of an error message when you run out of values (as in C4 and D4 in the above sample image).
In summary, use the VLOOKUP function to return values to the right of the value sought; use an INDEX/MATCH function pair to retrieve values from either side of the matching column.
这篇关于函数 VLOOKUP 的 Excel 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
