How to move Index column if blank in Vlookup in Google Sheets?
I am talking about moving the index column in Vlookup formula if the result is blank. This may be one of the dilemmas that every Vlookup users may face. No matter whether they are using Google Sheets or Excel.
Even if the search key is present in the lookup range, Vlookup may return blank. Because there may not be any value to return in the cell that in the row found.
Jump Index Column If Vlookup Returns Blank – Understanding the Problem
For example, I want to return value from column 2. But if the concerned cell in column 2 is blank, then I want the value from column 3 and so on.
How to check if the result is blank in Vlookup and if blank, move to the adjoining cell on the right?
I don’t want to manually change the index number in Vlookup, i.e. 2 here, to 3 to get the value from the third column. How to automate it?
I assure you, you are going to get one of the best solutions to solve this problem.
The below image conveys the whole picture of the problem, i.e, blank cell in the output range and move Index column if blank in Vlookup in Google Sheets.
Further, I have detailed the problem below with formula examples.
Definitely, I’ll share the Sheet that contains the above formula with you. Now let’s go deep into the problem and then to the solution.
Jump or Move Index Column If Blank in Vlookup in Google Sheets
I have already detailed the problem. Now I am trying to explain the problem with the help of the above sample data.
Vlookup and the Problem With Blank Output
The data range is A2: D. Lookup the range A2: A in this for the key “SP 2”. If found return value from Colum2 in the found row. If the output is blank, then return the value from the next column.
The Solution to Move Index Column If Blank in Vlookup Which is Not Working As Desired
This is a correctly structured Vlookup formula. But not for our above purpose.
=VLOOKUP("SP 2",A2:D,2,0)
This formula returns blank since there is no value in the Cell B3. Vlookup will search down the range A2: A and will find the key “SP 2” in A3.
The index column (the column to return the result from) is 2. So Vlookup will obviously return the value in the cell B3 which is blank.
How to automatically change the Index column to skip the blank cell? I mean how to move Index column if blank in Vlookup?
I have seen many users trying multiple Vlookup, mostly in Excel, in this case, and which is not recommended. It’s like this.
The Wrong Approach To Increment Index Column # in Vlookup
Formula:
=if(VLOOKUP("SP 2",A2:D,2,0)<>"",VLOOKUP("SP 2",A2:D,2,0),VLOOKUP("SP 2",A2:D,3,0))
I don’t recommend this since it’s not flexible. No doubt this formula can return the correct result. But there is a flexibility related issue. I’ll come to that later.
This Vlookup is working like this. There are two Vlookup formulas in this. Of course, you can see three. But it’s a repetition.
In the first formula the column Index is 2 and in the second it is 3. The IF logical statement tests the outputs of both the formulas.
If the first Vlookup returns a value (non-blank), then the IF will return that value, else the IF will execute the second Vlookup to return the value from a different column.
This formula has a disadvantage. It depends on the number of columns in your data range. The above formula can only return value from column 2 or 3.
If the first two columns return blank then for the third column, you may want to include one more Vlookup in the IF. It’s not an ideal solution in any way.
Here is my dynamic Vlookup formula that will correctly move Index column if blank in Vlookup. It can easily handle several columns!
The Correct Approach To Increment Index Column # in Vlookup
Here is my formula (master formula) that I have used to jump the Index column if the value is blank in Vlookup.
Master Formula:
=ArrayFormula(vlookup(F2,A2:D6,MATCH(false,ISBLANK(vlookup(F2,A2:D6,column(B2:D2),0)),0)+1,0))
In this, the search key is in cell F2. See this screenshot.
Master Formula Explanation:
First, see the (partially) generic formula.
=VLOOKUP(F2,A2:D,DYNAMIC COLUMN INDEX,0)
The below formula part replaces this “DYNAMIC COLUMN INDEX”
MATCH(false,ISBLANK(vlookup(F2,A2:D,column(B2:D2),0)),0)+1
This is a cool trick, right? Let me explain it.
You can use the MATCH function together with ISBLANK to return the first non-blank value in a column (works in a row also).
I have a very detailed tutorial on that. I recommend you to read that later – How to Return First Non-blank Value in A Row or Column in Google Sheets.
Formula Explanation: Match – Isblank – Vlookup – Column Combination
My above formula is in line with ‘that’ one. In the above MATCH formula, I am going to first explain the Vlookup that resides inside the ISBLANK.
When you use that Vlookup alone, you must use the ArrayFormula. Do you know why? Because you can see the function COLUMN replaces the Index number which requires ArrayFormula to work. See that below.
=ArrayFormula(vlookup(F2,A2:D6,column(B2:D2),0))
Here is it, in action.
Due to the use of the function COLUMN to replace the Index #, the Vlookup returns an entire range as output.
You can find how to use the COLUMN function in Vlookup in detail here – Dynamic Index Column in Vlookup in Google Sheets.
When you wrap this Vlookup with ISBLANK, it returns the result as below.
Formula:
=ArrayFormula(ISBLANK(vlookup(F2,A2:D6,column(B2:D2),0)))
Result:
TRUE TRUE FALSE
Since the first two cells in the output of the Vlookup are blank, the ISBLANK returns TRUE. That means the last FALSE contains a value.
Take a look at the above image. Cell B4 and C4 are blank but D4 contains a value.
In the MATCH formula, this ISBLANK and Vlookup combination acts as the range. Since the search key in the MATCH is the Boolean FALSE, it would return 3. That means the third column.
You should add #1 to this value to accommodate the column A. As per the just above screenshot example, the Index number returned by this combination formula is 4.
Conclusion
Hope I could make the formula explanation part understandable. Still, if you can’t make it straight get my Google Sheet below. Make your own experiment in that sheet in your leisure.
Google Sheets- Moving Index in Vlookup
That’s all about how to move Index column if blank in Vlookup in Google Sheets. Thanks for the stay, Enjoy.
Related Formulas:
1. Vlookup to Find Nth Occurrence in Google Sheets [Dynamic Lookup].
2. Examples to IF Vlookup Combination in Google Sheets.
Hi there!
Thanks for all you do!
This was super helpful to get me going. I have an issue with my formula. I keep getting the following error:
“MATCH range must be a single row or a single column.”
In my case, the search key is a range of names from one sheet, and I’m searching for those names in the current sheet.
Once the name is found, a value is returned. However, if the value in one row is blank, I want it to search the next row. Here is where I get the error because Match doesn’t appear to like it.
This is the formula I’m using:
=arrayformula(vlookup($F3:F,coinPrice!A2:C,match(false,isblank(vlookup(F3:F,coinPrice!A2:C,
column(coinPrice!$B2:$C2),0)),0)+1,0))
Any help will be greatly appreciated!
Hi, Julius Sea,
Can you share a Sheet with demo data?
You can use the “Reply” below. I won’t publish the comment.
Thanks for all your great formula help!
I’m trying to use Vlookup to get the last non-blank item in a row rather than the first one.
I tried to adapt your formula, but I can’t figure out how to get it to check in the other direction. Any help would be appreciated. 🙂
Hi, Todd Allis,
Here is a related Google Sheets tutorial – Vlookup to Get the Last Non-blank Value in a Row in Google Sheets.
Hi,
This is almost what I was looking for, just with a little more detail.
What if in your depicted table (with your VLOOKUP formula) I wanted to get the sum of the first 2 months, i.e., Jan and Feb. Also, if the Jan column is empty, get the sum of the subsequent 2 months.
Hi, Amit Kumar,
See the formula under “Filter Out Blanks from Vlookup Result Columns” in the below tutorial.
Filter Vlookup Result Columns in Google Sheets (Formula Examples)