In the "Fruit1.xlsx" window, select cell E2, enter 1 and move the mouse to the cell fill handle on the lower right corner of E2. (I) only require one column with the same dataġ. If you want to find the same data in the two tables that are in the "Fruit1.xlsx" and "Fruit2.xlsx" documents, the Sheet name is "Fruit Sale 1" and "Fruit Sale 2", the following is specific Search method:
#How to use vlookup in excel 2019 how to#
II, How to compare two excel sheets using vlookup
The formula = IFERROR (VLOOKUP (B2, $A$2: $A$8, 1, 0), "") and = IFERROR (VLOOKUP (B2, A2, 1, 0), "") difference: the former as long as AB two columns have Duplicate, it will return a duplicate value the latter will only return values if it is repeated on the same row. Hint: $A$2:$A$8 can also be represented by a column, ie A:A, but there are two problems with this, one is not supported by a lower version of Excel, and the other is that the execution speed may be slower.ģ. If VLOOKUP(B2,$A$2:$A$8,1,0) returns an error value, it returns a null value, otherwise it returns the value that is returned by VLOOKUP(B2,$A$2:$A$8,1,0). , and they are also found in A2 to A8.Ĭ, 1 is col_index_num of VLookUp, 0 means exact match when the formula is in C2, VLOOKUP(B2,$A$2:$A$8,1,0) means to find B2 (ie 13) in A2 to A8, After A2 is found, it returns to the first column of the range_lookup, which returns 13 in column A.ĭ, IfError is an error return function. $A$2 is an absolute reference to the column and row, dragging down will not become A3, A4., $A$8 and $A$2 are a meaning.ī, $A$2:$A$8 is the range_lookup, which means that B2 is searched from A2 to A8, B2 is changed to B3, B4. Formula =IFERROR(VLOOKUP(B2,$A$2:$A$8,1,0),"") descriptionĪ, B2 is the lookup value of VLookUp, which is a relative reference. Use the same method to return the rest of duplicates.
Double-click the cell C2, copy the formula =IFERROR(VLOOKUP(B2,$A$2:$A$8,1,0),""), and paste it in C2, press Enter, return to the search result 13, indicating the number of column A Same as the number of the B column in the second row select the C2 cell, move the mouse to the cell fill handle on the lower right corner of C2, and after the mouse changes to the bold black cross(+), double-click the left button to filter out all price duplicate values og the two columns.ĭouble-click the cell D2, copy the formula =IFERROR(VLOOKUP(B2,A2,1,0),""), and paste it in D2, press Enter, also return to 13. If you want to find duplicate values for two columns of prices. I, How to compare two columns in excel using vlookupġ. One is that the data of only one column of two tables has the same item, and the other is that all columns (ie, one row) of the two tables are the same. This article will introduce duplicate values of two columns and the same data for two tables(That is, the comparison of data of the two tables).įind duplicate values in two tables in excel using vlookup function introduces two examples.
One-to-many have been introduced in the previous chapter " VLookUp in excel introduces step by step(10 examples), include Reverse lookup,one-to-many lookup". How to find duplicate values in excel using vlookup? Finding duplicate values with VlookUp function can be divided into some items of one column are the same and data of the other column is different (that is, one-to-many), and the data that are the corresponding rows of two columns are the same, one or several fields or all fields(ie, one row) of the two tables are the same.
0 kommentar(er)
