Surely at some point you have organized project data in Excel and have wondered how to search multiple Excel sheets quickly so you don’t have to search manually. To do so, today we bring you this tutorial of two functions: the VLOOKUP function and the IF.ERROR function.
What are the VLOOKUP and IFERROR functions?
The VLOOKUP function searches our data range for the value we need, but if it doesn’t get that value among the ranges we give it from the same sheet, it throws the #N/A error. That’s why we use the IF.ERROR function, which allows us to choose what action to take if an error occurs.
The IF.ERROR function has two arguments. The first is the action that we want to perform, and the second is the action that we want to be performed in case the first throws an error. So, in an Excel workbook with two sheets, we should use a function that has this structure: ‘=IF.ERROR(VLOOKUP(Sheet1),VLOOKUP(Sheet2))’
As can be seen there, the first argument searches the first sheet of the book, and if the searched value is not found there, instead of displaying an error, the function would go on to search the second sheet of the book, as it appears in the second plot.
If we have more than two sheets, we need to know if the result of the second sheet throws an error, so we need to reuse the IF.ERROR function as follows: ‘=IF.ERROR(VLOOKUP(Sheet1),IF. ERROR(VLOOKUP(Sheet2),VLOOKUP(Sheet3)))’.
How to use VLOOKUP and IFERROR with two Excel sheets?
We will now show an example to illustrate what the syntax of these functions should look like. To do this, we must already have all our data properly organized in Microsoft Excel tables, and we must take into account the value of the rows and columns where these data are.
In our example, we have a list with data of the employees of a company in each sheet, and we want that, when looking for the name of the employee, it gives us how many years he has been in the company. The steps to follow to search for that data in two Excel sheets would be:
- Make sure that, on each sheet, the columns with the data are in the same place (for example, the name in column A, the years you have been with the company in column B).
- Know which cells are occupied (for example, columns A and B, from row 2 to row 10).
-
In a different sheet we are going to do the search, placing the name of the employee in a cell (for example, in cell B2), and in the cell immediately below it the following code: ‘=SI.ERROR(LOOKUP(B2,Sheet1! A2:B10,2,FALSE),VLOOKUP(B2,Sheet2!A2:B10,2,FALSE))’
In the code, B2 is the cell where we write the name we are looking for; Sheet1 is the sheet where we are searching, A2:B10 are the cells where the information is contained, 2 is the column that has the years of the employee in the company, and we put FALSE at the end so that the search is exact.
How to use VLOOKUP and IFERROR with three or more Excel sheets?
Now, to search three Excel sheets quickly, we need to follow essentially the same steps. We continue applying the same example of the list of employees and the years that each one has been with the company, using columns A and B, from row 2 to row 10 as before.
In the sheet where we will do the search, we place the name of the employee we want to search for again in cell B2 and in an adjacent cell we copy the following code: ‘=IF.ERROR(LOOKUP(B2,Sheet1!A2:B10,2,FALSE ),IF.ERROR(VLOOKUP(B2,Sheet2!A2:B10,2,FALSE),VLOOKUP(B2,Sheet3!A2:B10,2,FALSE)))’.
If your Microsoft Excel file has more sheets, just add more IF.ERROR functions as needed. However, it is not recommended to use too many functions, because it would make the process of getting an error more complicated.
On the other hand, there are many more functions in Excel that can help you in your day-to-day work in this program. Among these functions are:
- The COUNTIF function which is very useful.
- The database BASE function.
- The ATAN and ATAN2 functions.
- CSHC hyperbolic functions.
- The CODE function in Excel.
- The BESSELI and BESSELK equation function.
- The BDPRODUCT and BDAVERAGE functions are also very useful.
That is all for now! We hope these tricks work for you to speed up your searches in Excel tables. If you have found this post useful, do not hesitate to share it on your social networks with all your contacts.
