Methods of data reference in Excel

methods of data reference in Excel

The first step is to see if the target name is duplicated, without duplicate names in the data that needs to be referenced, you can use the VLOOKUP function, have duplicate names, and you can use the SUMIF function. As shown in the following illustration:

vlookup= (name A, data range A, column A of data, 0 or 1, the meaning of the function is expressed in "data range a" will and "name a" corresponding to the "column A data" reference, the last 0 for accurate reference, 1 for fuzzy Reference, the basic default is 0;

Note: When the number of series, must be from the data range of the first column, the number of the column name a must be the first column.

If you need to refer to more columns of data, you can add 123 outside the table yourself, so that you don't have to count the numbers every time you reference the data.

After you enter a formula, you can refer the data in the table on the left to the table on the right, as shown in the figure;

Note: The VLOOKUP function does not apply to the case where the same name a corresponds to two numeric values, in which case the system defaults to the first value and does not reference the second value.

When "name a" corresponds to 2 numeric values, there are two ways to do this, one is to use the SUMIF function to add 2 values to the penalty operation, and reference 2 numeric values. Another method is to modify the name of name a, and then use VLOOKUP to refer to it.

sumif= (corresponding to the name range, name a, data range), the function meaning is to sum the corresponding value in the data range with the same name as "name a" in the corresponding name range.

Summary: vlookup function, just a simple reference value. The SUMIF function can also be additive based on the reference.

However: SUMIF function, the main role in the addition operation, if the reference data is not a number, but words, then the SUMIF function is not available, and VLOOKUP functions, whether text or numbers can be referenced.

Compare the following figure

Attention matters

The VLOOKUP function and the SUMIF function, regardless of the function, must ensure that name A is exactly the same name as the data that needs to be referenced, otherwise the data cannot be referenced.