How to reference data that matches the conditions in other workbooks in Excel

There is a formula you can use:

lookup(1,0/((sheet1!$a$2:$a$101=$a2)*(sheet1!$c$2:$c$101=$c2)),sheet1!d$2:d$101)

<>3. Enter "=lookup(1,0 ((sheet1!) in the "d2" cell in table 2$a$2:$a$101=$a2)*(sheet1!

c$2:$c$101=$c2)),sheet1!d$2:

d$101)“。

4. You can get the corresponding citation results.

5. Use the downward filling method, and the citation is completed when you see the citation result.

1. In the first step, you need to create two new worksheets in the same excel**.

2. In the second step, enter "= the name of the referenced **" in the position where the data appears after the reference in the second **! The data location needs to be referenced".

3. In the third step, complete the above steps, and the quoted number will appear in the carriage enter.

4. Step 4, the same way, enter in the corresponding position: = Cited table! b2。

5. In the fifth step, click enter, and the quoted name will appear.

6. In the sixth step, the date of birth of the first ** can be quoted in the same way.

7. In the last step, follow the above method, and finally you can complete the ** after the citation. Excel data citation is complete.

Refer the eligible BAI data in Excel to another worksheet and the operation steps as described below:

1. First of all, open Excel and prepare to reference the data weights in Sheet1 to Sheet2, and the following figure is the data of Sheet1.

2. Secondly, enter the formula of the cells in the following figure in sheet2.

3. Finally, fill in the formula, as shown in the figure below. In this way, the data in sheet1 is referenced to sheet2.

Use the vlookup function.

1. The syntax rules of this function are as follows:

vlookup(lookup_value,table_array,col_index_num,range_lookup)

Parameter description: The lookup value is the value that needs to be searched in the first column of the data table. The lookup value can be a numeric value, a reference, or a text string.

A table array is a data table in which data needs to be found. Use a reference to the region or region name.

col index num is the sequence number of the data column in the table array. When the col index num is 1, the value of the first column of the table array is returned, when the col index num is 2, the value of the second column of the table array is returned, and so on. If the col index num is less than 1, the function vlookup returns an error value of value!

If col index num is greater than the number of columns in the table array, the function vlookup returns an error value of ref!.

Range lookup is a logical value that indicates whether the function vlookup looks for an exact match or an approximate match. If false or 0, an exact match is returned, and if it is not found, an error value of n a is returned. If Range Lookup is true or 1, the function vlookup will look for an approximate match, that is, if an exact match value is not found, it returns the maximum value less than the lookup value.

If the range lookup is omitted, it defaults to an approximate match.

2. Examples:

Manual lookups one by one are very cumbersome when the amount of data is large, so the vlookup function is used here to demonstrate:

First, enter "=vlookup(" in cell i4, then excel will prompt 4 parameters.

Presentation of vlookup results.

The first parameter, obviously, is that we want the 100003 to correspond to h4, and here we enter "h4,".

The second parameter, here we enter the area we are looking for (absolute reference), i.e. "$a$2:$f$12,";

The third parameter, "Total for the Year", is the sixth column of the region, so enter "6" here, and enter "5" to enter the fourth quarter project;

The fourth parameter, because we want to find the job number exactly, enter "false"."Or "0".

Finally, complete the last closing parentheses") "get the formula"=vlookup(h4,$a$2:$f$12,6,0)", and use the fill handle to fill other cells to complete the search operation.

There are many ways to use function formulas, and you need to know the structure and reference conditions of the two **s.

With the vlookup function, you can search by row, and you can search according to the forklift number field as a condition, and the result is that all the rows of cells with "warehouse" in column y are displayed in another location (or in another worksheet).

1. Relative and absolute citations.

References are references to values in workbooks, worksheets, and cell addresses.

Think of absolute quotes as dollars.

Add $ before the line, and the number of lines will not change when you copy the formula.

Add $ in front of the column, and the number of columns will not change when you copy the formula.

2. Use the multiplication formula table to deepen your memory.

3. Formula operation tips.

1. Hide and protect.

Select the formula—Right-click—Format Cells—Ctrl+1—Protect—Unformat—Review—Protect Worksheet—Enter Password—OK—The formula is no longer visible (if you want to cancel, just revoke the worksheet protection).

2. Quick replication of formulas.

Selected area—Ctrl+End; Ctrl+Up Arrow—Enter H197 (Define Name) in the Name Box — Shift+Enter — Selected Formula in the Bar—Ctrl+Enter.

3. Convert the formula to a numerical value.

Copy—Paste the value, or use the Quick Access bar to paste the value.

4. Merge cell copying formulas.

Function formula—max($d$26:26)+1—ctrl+carriage enter.

5. Quickly modify the formula.

Selected area —F2—Quick Modification.

6. Cite data from other workbooks.

1) Directly quote the data in this workbook with ='[Positioning.] xlsx]1.Basic usage'!$i$2

viii) Quick replacement formulas.

Select the region - Ctrl+H - Replace Content - Replace All (be careful not to replace the values in the function).

Fourth, the wrong reed posture error value returned by the formula, eight common problems.

5. The magic of shortcuts.

Select the region, alt+=, and quickly sum it in batches. (To be mastered).

6. Sum across worksheets.

Select the region - enter the formula =sum - select the first worksheet and press shift to the last table - press ctrl enter.

Material Data Teasing: Excel2010

1. Open an Excel2010 workbookFor example, there are two worksheets in this workbook, one is "2018" and the other is "2017".

2. Select the "2018" worksheet, click the C3 cell, and enter the formula symbol "=" in this cell

3. Now it is necessary to refer carefully to the data in the "2017" worksheet, so click the label of the "2017" worksheet with the mouse.

4. Then click the cell where the data bucket you want to reference is located in the "2017" worksheet. The data to be referenced here is cell B3.

<>6. Click the "Enter" button in the formula bar to confirm that the entered formula takes effect.

7. After the formula takes effect, return to the "2018" work, where the data in cell C3 is the data in the referenced "2017" worksheet.

In Excel, referencing data from other header worksheets can be done in two ways:

Use "=sheetname!".celladdress". In the loss, "sheetname" is the name of the worksheet to be referenced, and "celladdress" is the address of the cell to be referenced.

For example, if you want to reference the A1 cell data in a worksheet named "sheet2" in a worksheet named "sheet1", you can enter "=sheet1!" in the cell of "sheet2".a1", and press enter.

Use the mouse to drag and drop to make a reference. Select the cells you want to reference, and then enter "=" in the input box, then click the cells of the worksheet you want to reference, excel will automatically fill in the reference formula for you. For example, select the cells you want to cite data, enter "=" in the input box, switch to the worksheet you want to reference, select the cells you want to reference in the worksheet, and finally press the enter key to complete the reference air change.

Whichever way you use to refer, there are a few things to keep in mind:

Both the worksheet name and the cell address need to be entered correctly, otherwise an error will occur.

When referencing data from other worksheets, you need to ensure that the referenced worksheet is open.

If the referenced worksheet name contains spaces or special characters, you need to enclose the worksheet name with single quotation marks, such as "=".'sheet name'!a1”。

In Excel, you can set a name for a data region by using the Name Manager, which can then be referenced by name. The specific steps are as follows:

1. Select the data region you want to name, including row and column headers.

2. In the "Formulas" tab of Excel, click the "Name Manager" button to open the Name Manager dialog box.

3. Click the "New" button in the dialog box, enter a name, and select the address of the data area in "Reference".

4. Click the "OK" button to complete the operation of naming the data area.

Once you've completed the above steps, you can refer to the data region by name, e.g. in a formula"= Name"to refer to this data region, this formula can be used in other cells to display the contents of the data region.

Note that if you want to reference a named region in a different **, you need to precede the name with the worksheet name and exclamation mark, for example"sheet1!= Name"。