Use MATCH function to search location information

Uploaded time: October 14, 2021 Difficulty: Primary

Use MATCH function to search location information

Use MATCH function to search location information

Tips: After starting to play, you can adjust the video clarity, click

Quality

Auto 720p

Graphic skills

The MATCH function can return information about the location of the lookup content based on the lookup value. Take this spreadsheet as an example. We want to find the location of the column where the contact Andrew is located. Then we can use the MATCH function.

 

The syntax structure of MATCH function is: MATCH(lookup_value,lookup_array,match_type)

_1.gif


In this table, we need to find out which row Andrew is located in column A by his name in cell D3. Click the Formulas tab and the Insert Function button to find the MATCH function.

_2.gif


The first parameter, Lookup_value is the specified lookup object, here our lookup object is Andrew, so we enter D3 at the first parameter.

_3.gif


The second parameter, Lookup_array is the cell region or array containing the object to find. It should be noted that the cell region or array can only be one column or one row. If there are multiple rows and columns, the MATCH function will return the error value #N/A. Here, the Lookup_array is column A, which contains names. So we select the name area A1:A13 in column A.

_4.gif


The third parameter is Match_type, which is the matching pattern of lookup. The number 0 represents the exact match, and 1 represents the closest match. We input 0 here. Then, click OK to complete the settings.

_5.gif


The formula result is 6, which means that Andrew is in the sixth row of the A1:A13 cell range.

The return value 6 is the location information, which is related to the reference range of the second parameter. If we select A2:A13 at the second parameter, the return value is 5, which means the fifth row in the cell range A2:A13.

_6.gif


Make your work easier with WPS Spreadsheet like Microsoft Excel. We can use the MATCH function and the INDEX function in combination, making it easier to look up the value.

For more details about the MATCH function, you're welcome to enter WPS Academy. Did you get it?