Excel2007 How do I find the contents of a particular row and column cross cell?

This article is mainly about how to find the contents of a particular row and column cross cell in an Excel table, the actual work of the table is much larger, then this search method is very important. Here, I just use a very simple table as an excuse, let's take a look at the match function.

Match function

Meaning: Returns the position of the specified number in the specified array range

Syntax: MATCH (lookup_value, Lookup_array, Match_type)

Lookup_value: The value that needs to be looked up in the datasheet (Lookup_array).

Lookup_array: A contiguous range of cells that may contain a value to find, the range must be a row or a column, that is, one-dimensional data, and the lookup region of the reference is a one-dimensional array.

Match_type:

1 o'clock to find the position of the maximum number of values less than or equal to lookup_value in the Lookup_array, Lookup_array must be in ascending order:

0 o'clock to find the first numeric value equal to lookup_value, lookup_array in any order:

When-1, find the position of the minimum number that is greater than or equal to lookup_value in Lookup_array, Lookup_array must be sorted in descending order. With the Match function lookup function, when the lookup condition exists, the match function results in the exact position (numeric value), otherwise the #n/a error is displayed.

Operation Steps

1. Find the contents of a particular row and column intersection in a table, such as the intersection of B and I, the intersection of E and G

2, first determine the position of B in the heading.

Input =match (a13,d5:d9,0),

A13 is B,

The D5:D9 is the area to look for (a single column), and the area contains B. The best number is preceded by $, written as d$5:d$9, locked in the area, and the lookup area does not change when the formula is copied to the next grid.

0 indicates exact lookup

The result is 2, which represents I in the second row of the region.

3, now find I in the column position.

Input =match (c13,e$4:i$4,0),

The meaning of each parameter is the same as the previous step.

The result is 4, which represents I in the fourth column of the region.

4, it's time to find the intersection.

Find the intersection with the index function.

Input =index (E$5:I$9,D13,E13),

E$5:i$9 is the entire range of data to look for, and the intersection of B and I is within this scope.

D13 is the line position of B.

E13 is the column position of I

As a result, we found the intersection value of B and I, 5.

5, selected B→i row of these cells, the mouse to move to the lower right corner of the small cross, press the left button down, the formula is copied to the E→g line, also found the E and g of the intersection value of 6

It's that simple, I do it step-by-step, you can do it. All formulas are written in one cell, one at a time.