Search the Index function for multiple tables

• Search for multiple tables in the same Sheet

The Index function has two parameter forms, one of which supports multi-Table search. The parameter structure is as follows:

INDEX (reference, row_num, [column_num], [area_num])

In area_num, you can select the serial number of one of the tables.

For example, you can search for the price of 3rd rows and 2nd columns in two tables.

650) this. width = 650; "title =" 3.png" src = "http://www.bkjia.com/uploads/allimg/131227/1QK02029-0.png" width = "524" height = "181"/>

Index (reference, 100, 1) = 75, Index (reference, 2) =

The key is how the reference is made up? When you select a range of two tables, press ctrl to select two tables at the same time, that is, (E2: G10, K2: M10). Note that parentheses must be added when you select multiple tables, otherwise, the parameter Quantity cannot be calculated.

• Search for multiple table ranges on different sheets

In principle, Index does not support searching on different sheets. However, we can use other methods to cleverly solve this problem.

Essence 1: The reference range cannot select multiple table ranges on different sheets. Therefore, you can use the name method to represent multiple ranges. For example, the definition name abc represents the range of the same position on multiple sheets. The abc name can be calculated by the formula: OFFSET (a table &"! E2 "), 0, 0, 9, 3.

650) this. width = 650; "title =" 4.png" src = "http://www.bkjia.com/uploads/allimg/131227/1QK01C9-1.png" width = "261" height = "250"/>

Tip 2: the position of a table is not fixed. You can select a table in any range. Select a cell and enter the worksheet name each time. Use the Indirect function to parse the e2 cell under the name.

Formula: = OFFSET (INDIRECT (product price query table! $ I $1 &"! E2 "), 0, 0, 9, 3)

Enter the formula = INDEX (abc, 3, 2) in the cell to query the price)

Success!

 

This article is from the blog "create your own office" and is not reposted!