Solved

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

Posted on 2013-06-13
18
189 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 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
Technology Partners: 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 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
 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

739 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