Skip to content

How to Search by Row or Column in Excel? – Essential Formulas

23 de March de 2022

Microsoft Excel is one of the complete programs for editing spreadsheets. But it is also one of the few programs that has multiple functions that can be used together. Each of these functions allows us to perform an action within our Excel file. In the event that we wanted to search for data by row or column, we can use a function to find the data we need.

Which formula is best to look up values ​​in Excel?

One of the most used functions to find values ​​in an Excel file is the VLOOKUP function. However, this function only allows you to search for a value in the first column on the left.

In addition, there are also other functions that allow you to find data such as the MATCH function. But if what you need is search for values ​​or data by row or columnyou should know that you can use the VLOOKUP and MATCH functions together.

SEARCHV

It is one of the Functions of searching and referencing that owns the Microsoft Excel program. This function allows us to find any value in a database or search from one sheet to another.

The VLOOKUP function in Excel helps us find a matching value in the first column of our database. Also, that this function exclusively looks for the values ​​that they find to the right. So the searched value has to be in the first column of the database.

This function is very useful, when it is required to search in a single row or column and to find a value from the same position in a second row or column. The goal of this function is to find a value that matches the searched value.

The limitation that you can present when using the VLOOKUP function, is that nothing else can be used if your file contains its values ​​located on the left. So if your data is not located from left to right, this feature will not help you. In these cases you can choose to use the match function.

person typing on a laptop

COINCIDE

This function allows us to search for a certain value in a range of cells. The MATCH function in Excelit is very useful to find a value within a range of cells and returns us its relative position of the value in the range.

This function is very easy to use, since it allows us to obtain the row number that the searched value occupies. If you use this function, you can perform relative content searches in a spreadsheet.

Its limitation is that it only allows you search for values ​​within a given range. So using it will not show all the values ​​that match your search in your Excel file.

girl using a white laptop

How to use SEARCH and MATCH function together?

The VLOOKUP and MATCH functions can be used independently depending on the database of your Excel file. However, these functions can also be used together and thus you will have a wider search range.

It is important that you know that when using both functions, a data filter is produced in sets. This will allow you to broaden the search parameter. The VLOOKUP function is used to look up values ​​within a table and if you use the MATCH function, which will allow you to associate a data validation list with the VLOOKUP function.

To use both functions you need to have a database in your Excel workbook. You will then need to perform a series of steps that depend on the type of database you have and the value you want to find in your database.

If you want to search for values ​​with the VLOOKUP or MATCH functions, you need to locate yourself somewhere in the database. To make a search by row and by columnyou must use the MATCH function within the VLOOKUP function.

  1. Once you are located at a point in the database, you will have to go to the formula bar. where you should write “LOOKUP”and then enclose the rest of the formula in parentheses.
  2. When opening parentheses you must place the range of columns you want to search for. Example =VLOOKUP(B2, A2:D15)
  3. Followed by the search range of the VLOOKUP function, you will need to insert function MATCH.
  4. After the MATCH function, you’ll need to enclose the range of cells you want to search for in parentheses, followed by a zero at the end of the formula. Example =VLOOKUP(B2, A5:D15, MATCH ( B1, A4:D4,0).
  5. Finally, once you complete the structure of the formula, you need to put the “FALSE” function at the end of the formula. Example =VLOOKUP(B2, A5:D15, MATCH(B1, A4:D4,0),FALSE).