• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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

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
RWayneH
Asked:
RWayneH
  • 8
  • 5
  • 3
  • +1
1 Solution
 
Patrick MatthewsCommented:
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
 
helpfinderIT ConsultantCommented:
are you looking for something like this?
sample.xlsx
0
 
RWayneHAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
helpfinderIT ConsultantCommented:
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
 
Patrick MatthewsCommented:
Please post a sample file demonstrating what you want to do
0
 
RWayneHAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
RWayneHAuthor Commented:
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
 
Patrick MatthewsCommented:
Please explain why you can't use Conditional Formatting
0
 
RWayneHAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
Sorry I meant CF not CP.  -R-
0
 
limweizhongCommented:
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
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
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
 
limweizhongCommented:
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
 
limweizhongCommented:
By the way, you didn't accept my solution, the one you actually used.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now