Highlighting Vlookup Result Value in Google Sheets

Have you ever wanted to highlight specific values in your Google Sheets using the Vlookup function? Well, guess what? You don’t actually need Vlookup to do that! In this article, I’ll show you two alternative formulas that can achieve the same result without using Vlookup.

Option 1: Highlighting Multiple Occurrences

Let’s start with the first formula, which is super easy to use. Here’s how it works:

The formula to highlight Vlookup result value is:

=B3:B7=$G$3

In this example, we have a search key “Susan” in cell G3. We want to search for this name in the range A3:A7 and highlight the corresponding mark in the range D3:D7.

To apply this formula, simply use conditional formatting. If you’re new to conditional formatting in Google Sheets, don’t worry! I’ll guide you through it.

The beauty of this formula is its simplicity. It highlights the value 95 in cell D4, which corresponds to the search key “Susan”. However, keep in mind that this formula won’t work if the search key has duplicates in the search column.

Option 2: Mimicking Vlookup Output Perfectly

Now, let’s move on to the second formula, which perfectly mimics the output of the Vlookup function. Here’s how it works:

=address(row(),column(),4)=("D"&match($G$3,$B$3:$B$7,0)+2)

This formula is 100% accurate and works just like Vlookup. It highlights the desired value based on the search key. There are no drawbacks to using this formula.

A Flexible Vertical Lookup Formula That Highlights Results

If you’re looking for even more flexibility, I’ve got you covered. You can create a drop-down list in cell G3 using data validation, like this:

Next, create a second drop-down list in cell H3, like this:

Now, let’s modify the conditional formatting formula to make it even more flexible:

=address(row(),column(),4)=($H$3&match($G$3,$B$3:$B$7,0)+2)

With this formula, you can control the highlighting using the drop-down menus. Simply change the search key and the column to highlight using the drop-downs.

As you can see, learning Vlookup is essential for anyone working with spreadsheets. However, you don’t always need to rely on the Vlookup function itself. By using these alternative formulas, you can achieve the same results in a more flexible and intuitive way.

So go ahead and try out these formulas in your Google Sheets. Highlighting Vlookup result values has never been easier! And if you want more tips and tricks for Google Sheets, check out Crawlan.com for more valuable insights. Enjoy!