功能VLOOKUP的Excel问题 [英] Excel issue with Function VLOOKUP
问题描述
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月2日Tomo 6-Feb
25 6月2日Tomo 6-Feb
5 1-Jan Wash 1-Jan
14 1-Jan Wash 1-Jan
23 1-Jan Wash 1-Jan
所以假设它被标题为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） code>
并将其替换为4，它不断返回N / A。即使我将Date的格式更改为General，它仍然不返回任何内容。我不知道我在这里做错什么
VLOOKUP语法
功能配对此外，使用INDEX / MATCH比VLOOKUP更容易找出与名称相匹配的第二和第三个日期集。
INDEX和MATCH语法
和是用于显示一个空字符串（例如），而不是错误消息，当您用完数值（如上述示例图像中的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-JanSo 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屋！
