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 to search for values or data by row or column, you should know that you can use the VLOOKUP and MATCH functions together.
SEARCHV
It is one of the search and reference functions that the Microsoft Excel program has. 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.
COINCIDE
This function allows us to search for a certain value in a range of cells. The MATCH function in Excel is very useful for finding a value within a range of cells and returns the 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. Using this feature will allow you to perform relative content searches in a spreadsheet.
Its limitation is that it only allows you to search for values within a certain range. So using it will not show all the values that match your search in your Excel file.
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 do a search by row and by column, you must use the MATCH function within the VLOOKUP function.
- Once you are located at a point in the database, you will have to go to the formula bar. Where you must write “LOOKUP”, and then place the rest of the formula in parentheses.
- When opening parentheses you must place the range of columns you want to search for. Example =VLOOKUP(B2, A2:D15)
- Followed by the search range of the VLOOKUP function, you will need to insert the MATCH function.
- 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).
- Finally, once you complete the structure of the formula, you need to place the “FALSE” function at the end of the formula. Example =VLOOKUP(B2, A5:D15, MATCH(B1, A4:D4,0),FALSE).
