How can I use Google Sheets to return the column number of a match?

By /

Google Sheets is a powerful tool that allows users to organize and analyze data in the form of spreadsheets. One useful feature of Google Sheets is the ability to return the column number of a specific value or match within a dataset. This can be done by using the MATCH formula, which searches for a specified value in a range of cells and returns the position of that value within the range. By using this formula, users can easily locate and retrieve the column number of a desired match, making data analysis and manipulation more efficient and accurate. This feature is particularly useful for organizing large datasets and identifying patterns or trends within the data.

Google Sheets: Return Column Number of Match

You can use the following methods to return the column number of a match in Google Sheets:

Method 1: Return Column Number of Match within Table

=MATCH(G2, C1:E1, 0)

This particular formula will look up the value in cell G2 in the range C1:E1 and then return the column number that matches the value in cell G2.

Method 2: Return Column Number of Match within Entire Spreadsheet

=MATCH(G2, $1:$1, 0)

This particular formula will look up the value in cell G2 in the entire first row of the spreadsheetand then return the column number that matches the value in cell G2.

The following examples show how to use each method in practice with the following dataset in Google Sheets:

Note: The value of 0 in the last argument of the MATCH function tells Google Sheets to look for an exact match.

Example 1: Return Column Number of Match within Table

We can type the following formula into cell H2 to find the column number in the range C1:E1 that matches the value in cell G2:

=MATCH(G2, C1:E1, 0)

The following screenshot shows how to use this formula in practice:

Google Sheets return column number of match

The formula returns a value of 3 since the name “Assists” is located in column 3 of the range that we specified.

Example 2: Return Column Number of Match within Entire Spreadsheet

=MATCH(G2, $1:$1, 0)

The following screenshot shows how to use this formula in practice:

The formula returns a value of 5 since the name “Assists” is located in column 5 of the entire spreadsheet.

Note that the range $1:$1 in the formula indicates that we should look for the value “Assists” in row 1 of the entire spreadsheet.

Note: You can find the complete documentation for the MATCH function in Google Sheets .

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

x

Related terms:

  1. Does the Google Sheets formula return Yes or No if there is a match?
  2. What is the column number of a match in Google Sheets?
  3. How can I use INDEX MATCH to return the maximum value in Excel?
  4. Excel Formula: If Match then Return Yes or No
  5. How to Match Two Columns and Return a Third in Excel
  6. How Can INDEX MATCH Be Used to Return the Maximum Value in Excel?
  7. How can I use INDEX MATCH in Google Sheets to retrieve multiple results?
  8. # Check if One Column Value Exists in Another Column in Google Sheets
  9. How can I filter one column in Google Sheets based on another column?
  10. How can I count a column in Google Sheets if another column meets certain criteria?