To use the IF function across multiple sheets in Google Sheets, use the following formula.
Syntax=IF(ISNUMBER(MATCH(search_key,'sheet_name'!range,[search_type]),"Yes","No")search_key – The value to search for in the sheet mentioned.'sheet_name'!range – Indicates that you want to find and match the values from the specified range in the sheet mentioned.search_type -An optional argument, and can be 1, 0 or -1."Yes" is the value returned if the condition is true, i.e., there is a match for the word or number you to look for."No" is the value returned if the condition is false.
Example=IF(ISNUMBER(MATCH(A3,'Zoo 1'!$A$3:$A$18,0)),"Found in both the zoos","Found in only one of the zoos")
//This searches for the animal in cell A3 from the sheet named Zoo 1 in the range A3:A18, and returns as answer “Found in both the zoos” if a match is found.
Get a sample of the spreadsheet here.
The IF function is one of the common and useful operations in Google Sheets. It, as the word implies, is used to check whether a logical condition is true based on a given formula. It returns a specified value if the condition is true, and returns another value–or none, depending on what is specified in the formula–if the condition is false. You can find a list of functions in Google Sheets here, and the IF function here.
The IF function is commonly used to test if a certain condition is true and the result returned from the same spreadsheet. What most people are not aware of–or do not use–however, is that the IF function can be used across different and multiple sheets and with multiple conditions. This is exactly what you will learn in this article.
Why use the IF function across multiple sheets
When working with large amounts of data spread across multiple sheets, it becomes extremely tedious, if not difficult, to parse individual data from different sheets. The IF function can be used to remove the hassle by looking for values specified and returning a result based on the condition of the function.
Formula for the IF function across multiple Google Sheets
Suppose you are working on two sheets and you want to check if the other sheet contains a value the same as one in the sheet you’re working on. The formula will, then, be as shown under.
=IF(ISNUMBER(MATCH(search_key,'sheet_name'!range,[search_type]),"Yes","No")Where:
- search_key is the value you want to search for in the sheet mentioned
- ‘sheet_name’!range indicates that you want to find and match the values from the specified range in the sheet mentioned
- [search_type] is an optional argument, and can be 1, 0 or -1
- “Yes” is the value returned if the condition is true, i.e., there is a match for the word or number you to looked for, and
- “No” is the value returned if the condition is false.
How to use the IF function across multiple sheets
To use the IF function across multiple sheets in Google Sheets to find matching values in different sheets, follow the steps given below:
- Choose an empty cell on the sheet you’re working on
- Enter the formula =IF(ISNUMBER(MATCH(search_key,’sheet_name’!range,[search_type]),”Yes”,”No”)
- Press enter. And that’s it!
Replace the generic values in the formula above with your own values. If a match is found, “Yes” will be returned as the result; else, “No” will be returned.
Using the IF function across multiple sheets is not drastically different from or more complicated than using it in a single sheet. It just requires that you have the basic ideas of working with multiple sheets and the syntaxes involved. You just need to specify the sheet name in the formula, while ensuring that the rest of the syntax is not incorrect.
To get a better grasp of the function, let us consider an example and follow the procedure step by step.
You can also make a copy of the spreadsheets provided below so that you can follow step-by-step the instructions given here. Make a copy of the spreadsheet by clicking this link.
Step-by-step guide to use the IF function across multiple sheets
Step 1: Add data to the sheets
Let us suppose that we visited two different zoos and made a list of the animals we saw in the two zoos in two different spreadsheets and named them “Zoo 1” and “Zoo 2”, respectively. We want to find out which animals are found in both the zoos.
Step 2: Select the sheet to display the result
After the data have been added to the spreadsheets, we can choose either of the two sheets to work with on which we want the result displayed after searching for animals present in the sheet we are on as well as on the other sheet. Here, we choose the second sheet, i.e., the sheet named “Zoo 2”.
Step 3: Implement the formula
Enter the formula below in the cell to the right corresponding to the first animal on the sheet (here, B3):
=IF(ISNUMBER(MATCH(A3,'Zoo 1'!$A$3:$A$18,0)),"Found in both the zoos","Found in only one of the zoos")Here A3 is the animal in the cell that you want to find a match, A3:A18 is the range of the data in the sheet “Zoo 1” that you want to search for the matching values. The “$” symbols are added to make the cell reference absolute, ie, in order that the range specified does not change when the formula is applied to the rest of the animals.
Upon entering the formula and pressing enter, the result that is returned is: “Found only in one of the zoos” since hippopotamus is listed only in one of the sheets.
Step 4: Fetch the result
To find out the answer for the remaining animals, we can simply drag the cell to the last animal and check if the animal listed is found in both the zoos. The answer will be displayed on the corresponding cell to the right, as shown below.
Conclusion
We can thus easily find and match data across different sheets using the IF function without comparing the sheets and manually looking for each value individually. If, say, we have sales data for different products along with the customer names, we can use the IF function to compare and check which customers bought which, or some, or all of the products.
The example we have considered to demonstrate how to use the IF function across multiple sheets in Google Sheets is a simple one. The IF function can be used for different purposes with various conditions and can be nested with one or more IF functions or other functions for complex operations. That, however, is a topic for another day.
For an exhaustive list of tutorials on Google Sheets and how to perform various operations, visit our blog.
Similar articles:
XIRR function in Google Sheets
Conditional Formatting Based On Another Cell in Google Sheets – 3 Easy Steps
How to use COUNTIF function in Google Sheets | 2 min easy guide