Solved

Help with VLOOKUP formula? or Macro that will do...

Posted on 2013-06-13
18
178 Views
Last Modified: 2013-12-12
I would like a formuala copied down column T of the active sheet that would do a vlookup of the value in column B, and see if it is in column AI.

Copy the formula down column T as long as there is data in column B,

If there is a match, obviously the value will show in T, but I want that to turn green for good.
If there is no match, return the word ERROR, and turn the cell red in column T

Goal is for every row in column B that has data in it, column T will be either red or green.   Thanks -R-
0
Comment
Question by:RWayneH
  • 8
  • 5
  • 3
  • +1
18 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39245189
You might actually be better off with MATCH:

=IFERROR(INDEX(AI:AI,MATCH(B2,AI:AI,0)),"Error")

You could then use Conditional Formatting for the red/green.
0
 
LVL 19

Expert Comment

by:helpfinder
ID: 39245201
are you looking for something like this?
sample.xlsx
0
 

Author Comment

by:RWayneH
ID: 39245256
The sample file is close...  however the vlookup must be in column T.  Looks like a macro will be needed.  The rows will not align so I need to know if the value in column B is anywhere in column AI.  The formula needs to populate all the way down column T as long as there is a value in column B.  Hope this makes sense.

Match will not work, need to check the through column.   -R-
0
 
LVL 19

Accepted Solution

by:
helpfinder earned 500 total points
ID: 39245271
OK, I see.
then I modified for VLOOKUP, but unfortunately then I do not know how to make B values colored as you desire (hopefully someone else will help with this)
sample.xlsx
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39245398
Please post a sample file demonstrating what you want to do
0
 

Author Comment

by:RWayneH
ID: 39253079
Sorry I was gone over the weekend.  File is attached.

The two columns do not neccessary match and I need the formula copied down column T for as many cells that are in column B.  Hope this makes sense.. -R-
Sample1.xlsx
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39253371
The approach I outlined in the first comment will work just fine:

1) Put this formula in T3...

=IFERROR(INDEX(AI:AI,MATCH(B3,AI:AI,0)),"Error")

2) Copy that down through T???

3) Apply Conditional Formatting to those cells in Col T (one rule for does not equal Error, with green fill, and another rule for equals Error, red fill)

Q-28156533.xlsx
0
 

Author Comment

by:RWayneH
ID: 39253428
This does not copy the line down, and I cannot use conditional formatting in this case.  Is there a way to script this to fill in the formula?  I would prefer using the script please.  -R-
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39253440
Please explain why you can't use Conditional Formatting
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:RWayneH
ID: 39253471
Because I am using cell formatting throughout the rest of other code I am using and the clear cell formatting for the whole pg works very will.  I do not want to switch it now, and I want to keep it consistant...  want the code uses cell formatting vs conditional formating is it considered the same thing?   Like I said I prefer the cell formatting.  Thanks. -R-
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39253828
CF is basically an "overlay": while it does not remove "regular" cell formatting, if any of the CF rules come out as true for a given cell, the CF wins and overrides the normal settings.

Note that only the elements you define in the CF rule will override.  For example, if your regular cell formatting is applying bold text, and none of your CF rules affect the bold setting, even if the CF rule comes out true it will not "unbold" your text.

The huge benefit of CF is that it is dynamic.  If you use regular formatting to do your red/green, but then later there is a change in value that should cause a flip, regular formatting will not automatically update, but CF will.  CF is also more reliable than controlling this with a macro, because users can turn off macros, but turning off macros does not affect CF.
0
 

Author Comment

by:RWayneH
ID: 39253858
Can you please just understand, that I need cell formatting and not CP?  I totally understand the benefits of CP over cell formatting, but this is not my call and am doing as directed even though that way not be the best way to get the result it is what was asked for.  There are another procedures that hinge on this and well use it.  -R-
0
 

Author Comment

by:RWayneH
ID: 39256060
Sorry I meant CF not CP.  -R-
0
 
LVL 6

Expert Comment

by:limweizhong
ID: 39256993
What about this?
Public Sub DoStuff()
    Dim a&, n&, t, t2
    ActiveSheet.UsedRange
    n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
    For a = 3 To n
        t = Cells(a, 2)
        If t <> "" Then
            Cells(a, 20).FormulaR1C1 = "=isna(vlookup(rc2,c35,1,false))"
            If Not Cells(a, 20) Then
                Cells(a, 20) = t
                Cells(a, 20).Interior.Color = RGB(146, 208, 80)
            Else
                Cells(a, 20) = "ERROR"
                Cells(a, 20).Interior.Color = RGB(255, 0, 0)
            End If
        End If
    Next a
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 39259259
Why would the attached fail?  It seemed to be working fine and I was going to close this question, but for some reason this one failed.  Any ideas? and do we need to adj the code to allow for these types of files? -R-
BillCompare5864.xlsx
0
 

Author Comment

by:RWayneH
ID: 39259280
Can we make sure that column T is formatted as General prior to running?  It is the only difference I see in the cells that work versus the cells that do not.  The cells that do not work are formatted as text.  For some reason column T had mixed formats in it.  -R-
0
 
LVL 6

Expert Comment

by:limweizhong
ID: 39287998
Sorry, I didn't monitor this question.
Public Sub DoStuff()
    Dim a&, n&, t, t2
    ActiveSheet.UsedRange
    n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
    For a = 3 To n
        t = Cells(a, 2)
        If t <> "" Then
            Cells(a, 20).NumberFormat = "General"
            Cells(a, 20).FormulaR1C1 = "=isna(vlookup(rc2,c35,1,false))"
            If Not Cells(a, 20) Then
                Cells(a, 20) = t
                Cells(a, 20).Interior.Color = RGB(146, 208, 80)
            Else
                Cells(a, 20) = "ERROR"
                Cells(a, 20).Interior.Color = RGB(255, 0, 0)
            End If
        End If
    Next a
End Sub

Open in new window

I added line 8 which fixes it.

It might be better for line 9 to have "iserror" instead of "isna", because if column 20 contains #VALUE! then vlookup will return #VALUE! instead of #N/A, so the cell will not be marked "ERROR".
0
 
LVL 6

Expert Comment

by:limweizhong
ID: 39715925
By the way, you didn't accept my solution, the one you actually used.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now