VLOOKUP with IF function

[BBCC0000]

Hi all ,

May I know how do I create a macro that applies to Column H, starts from H2 to the last value with the following criteria:-

It will VLOOKUP from cell G2 to the last,
1) If the value of G2 equals to the Column A, it will show TRUE with Green highlight;
2) If the value of G2 equals to the Column D, it will show FALSE with Red highlight;
3) If the value of G2 doesn't equal to Column A and D, it will show CHECK with Yellow highlight.

Can refer to the image for reference.

Thank you for reading my query! Hope you can help me on solving this macro.

 

[bebo021999]

Try :

=IF(COUNTIF(A:A,G2),"TRUE",IF(COUNTIF(D:D,G2),"FALSE","CHECK"))

 

[Peter_SSs]

how do I create a macro that applies to Column H, starts from H2 to the last value with the following criteria:-

Give this a try with a copy of your workbook.

Sub CorrectWrong()
  Dim i As Long
  
  With Range("H2:H" & Range("G" & Rows.Count).End(xlUp).Row)
    .Formula2 = "=IF(ISNUMBER(MATCH(G2,A:A,0)),""TRUE"",IF(ISNUMBER(MATCH(G2,D:D,0)),""FALSE"",""CHECK""))"
    .Value = .Value
    For i = 1 To .Rows.Count
      Select Case .Cells(i).Text
        Case "TRUE": .Cells(i).Interior.Color = vbGreen
        Case "FALSE": .Cells(i).Interior.Color = vbRed
        Case Else: .Cells(i).Interior.Color = vbYellow
      End Select
    Next i
  End With
End Sub

 

[BBCC0000]

Give this a try with a copy of your workbook.

Sub CorrectWrong()
  Dim i As Long
 
  With Range("H2:H" & Range("G" & Rows.Count).End(xlUp).Row)
    .Formula2 = "=IF(ISNUMBER(MATCH(G2,A:A,0)),""TRUE"",IF(ISNUMBER(MATCH(G2,D:D,0)),""FALSE"",""CHECK""))"
    .Value = .Value
    For i = 1 To .Rows.Count
      Select Case .Cells(i).Text
        Case "TRUE": .Cells(i).Interior.Color = vbGreen
        Case "FALSE": .Cells(i).Interior.Color = vbRed
        Case Else: .Cells(i).Interior.Color = vbYellow
      End Select
    Next i
  End With
End Sub

Thanks for your reply. I encountered this error when I execute the code:

It stuck at this row:
.Formula2 = "=IF(ISNUMBER(MATCH(G2,A:A,0)),""TRUE"",IF(ISNUMBER(MATCH(G2,D:D,0)),""FALSE"",""CHECK""))"


Is it the version issue?

 

[Alex Blakenburg]

Your profile says you have MS365, are you running it on a MS365 machine ?
That formula doesn't actually need Formula2.
Try changing it to just Formula and see if that fixes it.