• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

General VLOOKUP question

L

Leigh Digons

Member
What would be the purpose of having an absolute cell reference instead of a column number in a VLOOKUP formula?

Example: =VLOOKUP(A9,$AC$9:$CR$20,25,FALSE)
vs.
=VLOOKUP(A9,$AC$9:$CR$20,$CR$8, FALSE)

where $CR$8 = 68
and column CR has 1 in CR9, 2 in CR10, 3 in CR11

The conditional format formula is to use red text if =VLOOKUP(A9,$AC$9:$CR$20,$CR$8,FALSE) is true. This is doing what the opposite of what I would expect. A9 = "Jan" which equals 1, while CR9 = 1, and so on. But the text turns red when the formula is false, i.e. when Jan (1) > 3
 
Here's a sample file. The conditional formatting for the red text is doing the opposite of what I would expect it to do. The conditional formatting should return FALSE and keep it black, but it turns it red instead. I want it to be red for months after the current month.
 

Attachments

  • test2.xlsx
    56.9 KB · Views: 6
1. Reason for having Cell Reference instead of number.
When the cell you reference has formula, it can change depending on other conditions. Which can make Vlookup to dynamically look up column instead of looking at static column only.

2. Delete all the CF in there. Then change CF for C9:C20 range to following. You are referencing wrong range (AC:CF) and it's causing VLOOKUP to evalute to #Value error.

Formula should be...
=VLOOKUP($A9,$AC$9:$CR$20,$CR$8,FALSE)>$AJ$1
 
Basically what it's doing is looking up Values in A9:A20 (whichever is corresponding to row in question), against left most column of lookup range (table_array) and returning corresponding row of Column specified.

For CF formula in C9 is looking for "Jan" in range AC9:AC20.
Which evaluates to 1st row in range (AC9). Then it looks for corresponding value in CR9:CR20 (which is 68th column from AC), in this instance it's CR9 so 1.

There are multiple method to achieve CF condition. I'd go about it bit differently myself.
Remove previous CF from C9:C20 range.

Select C9:C20 range and create new CF with following formula.

=MONTH($A9&1)>$AJ$1

By tacking on 1 at end of "mmm" (ie, Jan to Dec) it converts text string to format excel can interpret (i.e. Jan1 = 1/1/2016). MONTH() extracts month# from date. See recent article in this site (tip #023.) below.
http://chandoo.org/wp/2016/05/19/excel-tips-tricks-cheats-hacks-readers-edition/
 
You must log in or register to reply here.
Back
Top