In order to highlight Vlookup result value, the function Vlookup is not necessary for Google Sheets. I have two different formulas for you and none of them using Vlookup.
I’ll come to the formulas later. First, understand the peculiarities of the two formulas that you are going to learn.
There may be multiple occurrences of the Vlookup search key in your data range (first column). In that case, the formula option #1 would highlight all the corresponding cells.
But my second formula would exactly behave like a Vlookup. It would only highlight one single cell.
Why these much talk about Vlookup?
You may have seen plenty of tutorials on the use of Vlookup online. In my blog also you can find lots of different variations of the Vlookup formula (please use the search key on the navigation bar).
If somebody takes an interview of you to check your Google Sheets or Excel skills, they will definitely ask about Vlookup.
It’s a fact that many Spreadsheet users are not familiar with using Vlookup. But they may definitely be heard about the use of this function.
So learning Vlookup is an essential thing in Spreadsheets. Having said that I am taking you back to the highlighting part.
Highlight Vlookup Result Value in Google Sheets
I am starting with the formula # 1 which is easy to use.
Option 1:
The formula to highlight Vlookup result value:
=B3:B7=$G$3
Cell G3 contains the search key “Susan”. I want to search this name in the range A3: A7 and highlight the mark of this person in the range D3: D7.
You need to just apply this formula in the conditional formatting rule. If you are new to Google Sheets conditional formatting, here is how to do it.
Conditional formatting is residing under the menu Format. Refer to this pic.
This is the simplest way of highlighting a Vlookup result value in Google Sheets. The above formula highlights the value 95 in cell D4.
To extract this value in the cell, here is the Vlookup.
=vlookup(G3,B3:D7,3,0)
Pros and Cons of the above Conditional Formatting Formula.
Pros:
Very easy to understand and use.
Cons:
Not useful if the search key has duplicates in the search column.
Option 2:
This is the perfect formula that exactly mimics the Vlookup output but in the form of highlighting.
Formula:
=address(row(),column(),4)=("D"&match($G$3,$B$3:$B$7,0)+2)
This formula has already featured in one of my earlier tutorials. So please read that post to understand this formula better – Highlight Intersecting Value in Google Sheets in a Two Way Lookup.
To apply this conditional formatting formula, follow the below screen capture.
This formula is 100% accurate and works similar to Vlookup.
Pros:
The perfect formula to Highlight Vlookup Result Value in Google Sheets.
Can make flexible (find the detail below).
Cons:
There are no drawbacks.
A Flexible Vertical Lookup Formula That Highlights Results
This is not a new formula. I am converting my above second formula to a ‘more’ flexible one.
Step 1: Create a drop-down list in cell G3 using data validation. You can find the option under the Data menu.
Step 2: Then create a second drop-down list in cell H3.
Now see the conditional formatting formula. Apply this in the conditional formatting.
=address(row(),column(),4)=($H$3&match($G$3,$B$3:$B$7,0)+2)
Now you can control the highlighting using the drop-down.
Just change the search_key using the drop-down menu and also the column to highlight.
What’s the difference in this formula compare to the previous one?
Instead of the letter “D”, I have used the cell reference $H$3 this time in this formula.
Use any of the formulas above to highlight Vlookup result value in Google Sheets. Enjoy!
