Hey everyone! I’m working on a Google Sheets project and I need some help. I’ve got two sheets set up. Sheet 1 has my raw data and Sheet 2 is where I’m using VLOOKUP to pull in the info.
Here’s my problem: Sometimes the data in Sheet 1 is the word “empty”. When that happens, I want the cell in Sheet 2 to actually be blank instead of showing “empty”.
I’ve tried a few things but can’t quite figure it out. Any ideas on how to combine VLOOKUP with an IF statement to make this work?
I made a sample sheet to show what I mean. It’s pretty straightforward - just two columns in each sheet. Thanks for any help you can give!
hey RunningTiger, try this formula:
=IF(VLOOKUP(A2,Sheet1!A:B,2,FALSE)=“empty”,“”,VLOOKUP(A2,Sheet1!A:B,2,FALSE))
it checks if the vlookup result is “empty” and returns blank if true, otherwise gives the vlookup value. hope this helps!
I’ve encountered a similar situation in my work. Here’s a solution that should do the trick:
=IFERROR(IF(VLOOKUP(A2,Sheet1!A:B,2,FALSE)=“empty”,“”,VLOOKUP(A2,Sheet1!A:B,2,FALSE)),“”)
This formula first performs the VLOOKUP, then checks if the result is “empty”. If it is, it returns a blank cell. If not, it returns the VLOOKUP result. The IFERROR function wraps everything to handle any potential errors, returning a blank cell if an error occurs.
This approach is efficient and handles multiple scenarios. Let me know if you need any clarification on how it works.
I’ve dealt with this issue before, and there’s actually a more streamlined approach you can take. Instead of nesting functions, you can use the FILTER function combined with IFNA. Here’s the formula:
=IFNA(FILTER(Sheet1!B:B, Sheet1!A:A=A2, Sheet1!B:B<>“empty”), “”)
This formula does a few things in one go. It filters the data from Sheet1 based on two conditions: matching the lookup value in A2 and not being “empty”. If no match is found, it returns a blank cell.
The beauty of this method is that it’s more efficient for larger datasets and easier to read. Plus, it automatically handles the case where there’s no match without needing an extra IFERROR wrapper.
Give it a try and see if it works for your specific setup. Let me know if you need any tweaks!