Lookup Formula

[Roushcj]

Hello, I am looking to see if there is a formula that will give me the values in column C below. What I am looking to do is if the Transaction ID is the same and there is no red step in column B then code all of column c as igo for that transaction ID. I used a vlookup but am potential going to use this data in another system that doesn't us vlookups. The actually data does not have coloring but is indicated by the word NIGO on the Step name.

A​	B​	C​
Transaction ID	Steps	IGO/NIGO
1234	Step 1	IGO
1234	Step 2	IGO
1234	Step 3	IGO
5678	Step 1	NIGO
5678	Step 2	NIGO
5678	Step 3	NIGO
5678	Step 4	NIGO
5678	Step 5	NIGO
5678	Step 6	NIGO

 

[ZMyrrh]

Unfortunately, you can't make a formula that uses font color in the formula itself. You can however make code that uses font color.

This code does the trick.

Public Sub IGONIGO()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim n As Integer
Dim IGO As String
Dim NIGO As String

For i = 2 To WorksheetFunction.CountA(Range("a1:a1000"))
   
    IGO = ""
    NIGO = ""
   
    j = WorksheetFunction.CountIf(Range("a2:a1000"), Cells(i, 1).Value) - 1
   
    m = Cells(i, 1).Value
   
  
   
    For k = 0 To j
       
        If Cells(i + k, 1).Value = m And Cells(i + k, 2).Font.ColorIndex = 1 Then
       
        IGO = "IGO"
       
        ElseIf Cells(i + k, 1).Value = m And Cells(i + k, 2).Font.ColorIndex <> 1 Then
        NIGO = "NIGO"
        End If
       
   
    Next k
   
    For n = 0 To j
   
    If NIGO <> "" Then
        Cells(i + n, 3).Value = NIGO
    Else
        Cells(i + n, 3).Value = IGO
    End If
   
    Next n
   
    i = i + j
Next i

End Sub

 

[ZMyrrh]

This will work as long as you don't change what columns these pieces of data are in.

 

[Roushcj]

Unfortunately, you can't make a formula that uses font color in the formula itself. You can however make code that uses font color.

This code does the trick.

Public Sub IGONIGO()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim n As Integer
Dim IGO As String
Dim NIGO As String

For i = 2 To WorksheetFunction.CountA(Range("a1:a1000"))
  
    IGO = ""
    NIGO = ""
  
    j = WorksheetFunction.CountIf(Range("a2:a1000"), Cells(i, 1).Value) - 1
  
    m = Cells(i, 1).Value
  
 
  
    For k = 0 To j
      
        If Cells(i + k, 1).Value = m And Cells(i + k, 2).Font.ColorIndex = 1 Then
      
        IGO = "IGO"
      
        ElseIf Cells(i + k, 1).Value = m And Cells(i + k, 2).Font.ColorIndex <> 1 Then
        NIGO = "NIGO"
        End If
      
  
    Next k
  
    For n = 0 To j
  
    If NIGO <> "" Then
        Cells(i + n, 3).Value = NIGO
    Else
        Cells(i + n, 3).Value = IGO
    End If
  
    Next n
  
    i = i + j
Next i

End Sub

Unfortunately, you can't make a formula that uses font color in the formula itself. You can however make code that uses font color.

This code does the trick.

Public Sub IGONIGO()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim n As Integer
Dim IGO As String
Dim NIGO As String

For i = 2 To WorksheetFunction.CountA(Range("a1:a1000"))
  
    IGO = ""
    NIGO = ""
  
    j = WorksheetFunction.CountIf(Range("a2:a1000"), Cells(i, 1).Value) - 1
  
    m = Cells(i, 1).Value
  
 
  
    For k = 0 To j
      
        If Cells(i + k, 1).Value = m And Cells(i + k, 2).Font.ColorIndex = 1 Then
      
        IGO = "IGO"
      
        ElseIf Cells(i + k, 1).Value = m And Cells(i + k, 2).Font.ColorIndex <> 1 Then
        NIGO = "NIGO"
        End If
      
  
    Next k
  
    For n = 0 To j
  
    If NIGO <> "" Then
        Cells(i + n, 3).Value = NIGO
    Else
        Cells(i + n, 3).Value = IGO
    End If
  
    Next n
  
    i = i + j
Next i

End Sub

Is there a way to do it with only formulas like an if statement. I will have to move this into a program like excel that doesn't have vba capabilities. The color coding of red was just to show that step was nigo. In the actual data I would be looking for the word nigo. For example if on step for ID 5678 has the word nigo in column b I want all lines for ID 5678 to be the word nigo in column c.

 

[ZMyrrh]

Could you give a few more examples? I am seeing that you want the IGO or NIGO to show up if in the same column and same transaction ID that you want it to be whatever the other IGO or NIGO.

 

[Roushcj]

Could you give a few more examples? I am seeing that you want the IGO or NIGO to show up if in the same column and same transaction ID that you want it to be whatever the other IGO or NIGO.

Here is another sheet with a little less generic data. You can see on line 6, 7, 8 the Category is NIGO because one of these lines exist for Transaction ID 1234 then the result in column C for transaction ID 1234 is NIGO for all of them. This is the same for 8765. For 5678 and 2121 the result is IGO because the no category is NIGO for each of these ids. I am looking to see if there is a formula for column C that is not a VLOOKUP that can look at all the same ID for column A then look at column B and add the correct word IGO or NIGO in column C. Thanks

	A	B	C
1	Transaction ID	Category	Result
2	1234​	Review	NIGO
3	1234​	Review	NIGO
4	1234​	Cashiering	NIGO
5	1234​	Cashiering	NIGO
6	1234​	NIGO	NIGO
7	1234​	NIGO	NIGO
8	1234​	NIGO	NIGO
9	1234​	Cashiering	NIGO
10	5678​	Review	IGO
11	5678​	Review	IGO
12	5678​	Internal Review	IGO
13	5678​	Internal Review	IGO
14	5678​	New Account Setup	IGO
15	5678​	Tech Setup	IGO
16	5678​	New Account Setup	IGO
17	5678​	Tech Setup	IGO
18	2121​	Review	IGO
19	2121​	Review	IGO
20	2121​	Internal Review	IGO
21	2121​	Internal Review	IGO
22	2121​	New Account Setup	IGO
23	8765​	Review	NIGO
24	8765​	Review	NIGO
25	8765​	Cashiering	NIGO
26	8765​	Tech Setup	NIGO
27	8765​	Tech Setup	NIGO
28	8765​	Cashiering	NIGO
29	8765​	NIGO	NIGO

 

[ZMyrrh]

This formula worked for me. Let me know if this works for your situation!

=IF(COUNTIF(INDIRECT(ADDRESS(MATCH(A2,$A$1:$A$29,0),2,4,1)&":"&ADDRESS(MATCH(A2,$A$1:$A$29,1),2,4,1,)),"NIGO")>0,"NIGO","IGO")

 

[Roushcj]

This formula worked for me. Let me know if this works for your situation!

=IF(COUNTIF(INDIRECT(ADDRESS(MATCH(A2,$A$1:$A$29,0),2,4,1)&":"&ADDRESS(MATCH(A2,$A$1:$A$29,1),2,4,1,)),"NIGO")>0,"NIGO","IGO")

This does work thank you very much.