Combining IF and VLOOKUP in Google Sheets lets you perform lookups conditionally. You can switch lookup tables, evaluate lookup results, and control lookups with checkboxes. This guide covers three practical examples.
Switch Lookup Tables with IF and VLOOKUP
Sometimes you may want to switch the lookup table in VLOOKUP dynamically, for example, based on the value in another cell. You can achieve this by combining IF with VLOOKUP.
=VLOOKUP(search_key, IF(control_cell=control_value, range1, range2), index, is_sorted)Here’s an example.
Assume you have two tables: one containing data about fruits and the other about vegetables.
Table 1 is in the range A3:B7, and Table 2 is in D3:E6. In both tables, the first column contains the item names, and the second column contains their prices. You want to look up the price of an item from the appropriate table.
The control cell is H3, which contains either “Fruits” or “Vegetables”, and the search key is entered in H4.
Use the following formula:
=VLOOKUP(H4, IF(H3="Fruits", A3:B7, D3:E6), 2, FALSE)The IF function switches the lookup range based on the value in H3. If H3 contains “Fruits”, VLOOKUP searches A3:B7; otherwise, it searches D3:E6.
Alternative Solutions
You can also create two named ranges, fruits and vegetables, and then use the following formula:
=VLOOKUP(H4, INDIRECT(H3), 2, FALSE)If you’re using XLOOKUP, a nested XLOOKUP provides another simple approach:
=XLOOKUP(H4, A3:A7, B3:B7, XLOOKUP(H4, D3:D6, E3:E6, "", 0), 0)The outer XLOOKUP searches the Fruits table first. If the item isn’t found, the if_not_found argument evaluates the inner XLOOKUP, which searches the Vegetables table.
You can learn more about this approach in Nested XLOOKUP Function in Google Sheets.
Evaluate VLOOKUP Results with IF
In Google Sheets, comparison operators (and their equivalent functions) are commonly used to compare values and evaluate conditions.
| Operator | Symbol | Equivalent Function |
|---|---|---|
| Equal to | = | EQ |
| Not equal to | <> | NE |
| Greater than | > | GT |
| Less than | < | LT |
| Greater than or equal to | >= | GTE |
| Less than or equal to | <= | LTE |
Here’s how to combine IF and VLOOKUP with a comparison operator.
Suppose the range A2:B9 contains student names in column A and their total attendance in column B. The student’s name is entered in D2.
To retrieve the attendance, use:
=VLOOKUP(D2, A2:B9, 2, FALSE)Now, suppose a student is eligible to take the examination only if their attendance is greater than 220. You can combine IF with VLOOKUP to evaluate the returned value:
=IF(VLOOKUP(D2, A2:B9, 2, FALSE)>220, "Allowed", "Debarred")Alternatively, you can use the equivalent GT function instead of the > operator:
=IF(GT(VLOOKUP(D2, A2:B9, 2, FALSE), 220), "Allowed", "Debarred")
Both formulas return “Allowed” when the student’s attendance exceeds 220; otherwise, they return “Debarred”.
Control VLOOKUP with a Checkbox and IF
You can use a checkbox to enable or disable a VLOOKUP result in Google Sheets.
First, insert a checkbox by selecting the desired cell and choosing Insert > Tick box.
Suppose the checkbox is in D2, and the lookup table is in A:B. Enter the following formula in E2:
=IF(D2, VLOOKUP("apple", A:B, 2, FALSE), "")When the checkbox is checked, the formula returns the lookup result; otherwise, it returns a blank.
Conclusion
In this guide, we explored several practical ways to combine IF and VLOOKUP in Google Sheets. You learned how to switch lookup tables dynamically, evaluate lookup results using comparison operators, and enable or disable a VLOOKUP with a checkbox.
If your goal is to handle VLOOKUP errors, consider using IFNA or IFERROR, depending on the type of error you want to handle.
Hi Prashanth,
I followed up last time, but I think you missed it twice.
Can I have an update for this, or will you be able to reproduce it?
Thanks
Hi, Guenahel,
I left a comment within your Sheet because I was unsure about the problem you need to solve because of data in several tabs.
But I didn’t receive any response to my comment.
If I remember correctly, I have already solved two problems in that Sheet.
Please share a sample sheet with data ONLY in one tab and your expected result in another for further assistance. It can save my time.
If you want to solve several problems, It’s high time to hire someone.
Hi Prasanth,
Hope you are well.
Gentle follow up on my previous messages. I hope you can help me out. would really mean a lot for me
Hi, Guenahel,
Please share the sheet once again. I’ll check once free.
Hi Prashanth,
What you’ve done was pretty much amazing and helpful!
The data from the “General” Sheet should be pulled from the “Sample” sheet. If that work, the KVP Testing sheet will be flawless.
Here’s the Spreadsheet link for your reference: – URL removed by admin –
Hi, Guenahel,
That’s not matching your first requirement/query.
Having said that, please check the test_2 tab for a suggested formula.
Thank you.
I tried Query and using multi OR, but it doesn’t help much as it still filters unnecessary data that it shouldn’t.
Here’s the editable spreadsheet: – URL removed by admin –
Hi, Guenahel,
It’s difficult to manipulate data when there are merged cells.
So you should remove them physically or virtually.
I have virtually removed those merged cells and applied the Query.
Related:- How to Fill Merged Cells Down or to the Right in Google Sheets.
Check my tab “kvp testing” for the solution.
I’m trying to see how I can work out the Vlookup function and IF statement with my spreadsheet.
I want column C in the “Summary” Sheet to pull data from the “General” Sheet.
Here is my initial formula:
=VLOOKUP(A4,General!B2:M13,4)I’m trying to search that matches the Column “Summary A” on column “General B.”
When it matches, I want this to get the VA on column ” General D” only if column “General E” has a check or TRUE value.
Hi, Guenahel,
Thanks for your Sheet though it’s only viewable.
I’m suggesting you an array formula. Empty the range C3:C in the “Summary” tab.
Then insert the below Vlookup in C3.
=ArrayFormula(IFNA(vlookup(A3:A,filter(General!B2:D,General!E2:E=true),3,0)))Don’t copy it down.
Thank you so much! It worked!
Can you help me a bit more? There are still some things I can’t work out.
Is there any chance we can do screen sharing?
Hi, Guenahel,
Sorry! My time zone and busy schedule don’t allow me to do the same.
You can share a sample sheet as earlier with your notes in that.