Move Index Column If Blank in Vlookup in Google Sheets

Are you struggling with moving the index column if it’s blank in a Vlookup formula in Google Sheets? Don’t worry, you’re not alone. Many users face this dilemma, whether they’re using Google Sheets or Excel.

Even if you have the search key present in the lookup range, Vlookup may still return a blank. This happens when there is no value to return in the cell found in the row.

But fret not! I have one of the best solutions for you to solve this problem. Let me explain it step by step.

Understanding the Problem: Jump Index Column if Vlookup Returns Blank

Let’s say you want to return a value from column 2. However, if the concerned cell in column 2 is blank, you want to retrieve the value from the next column, column 3, and so on.

The question is, how can you check if the result is blank in Vlookup and, if it is, move to the adjoining cell on the right? You don’t want to manually change the index number in Vlookup every time.

The Solution: Jump or Move Index Column If Blank in Vlookup in Google Sheets

Here’s a formula that can solve your problem and easily handle several columns!

=ArrayFormula(vlookup(F2,A2:D6,MATCH(false,ISBLANK(vlookup(F2,A2:D6,column(B2:D2),0)),0)+1,0))

In this formula, the search key is in cell F2. The trick lies in using the MATCH function together with ISBLANK to return the first non-blank value in a column.

Let me explain the formula in detail:

1. The inner VLOOKUP with COLUMN as the index number alternative returns an entire range as output.
2. When you wrap this VLOOKUP with ISBLANK, it returns TRUE for blank cells and FALSE for cells with a value.
3. The MATCH formula uses this ISBLANK and VLOOKUP combination as the range. It returns the column number of the first cell with a value.
4. Adding 1 to the column number accommodates column A.
5. The final VLOOKUP formula uses the result of the MATCH formula as the index column to retrieve the value.

And there you have it! A dynamic formula that automatically moves the index column if it’s blank in Vlookup.

Conclusion

I hope I was able to break down the formula and make it understandable for you. If you’re still confused, you can always check out my Google Sheet where you can experiment and learn at your own pace.

That’s all about how to move the index column if it’s blank in Vlookup in Google Sheets. If you have any questions, feel free to reach out. Enjoy exploring and improving your Sheets skills!

Check out Crawlan.com for more useful tips and tricks on Google Sheets!

Related Formulas:

1. Vlookup to Find Nth Occurrence in Google Sheets [Dynamic Lookup]
2. Examples to IF Vlookup Combination in Google Sheets
3. Vlookup and Hlookup Combination In Google Sheets
4. Use of COLUMNS Function in Vlookup in Google Sheets