Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-06-13
18
Medium Priority
?
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
  • +1
18 Comments
 
LVL 93

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
helpfinder earned 2000 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 93

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 93

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 93

Expert Comment

by:Patrick Matthews
ID: 39253440
Please explain why you can't use Conditional Formatting
0
 

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 93

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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

721 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