Compare Two cells, Obtain a cell in the same row, and do this over multple cells

[EvanNoble]

Hi all, how could I write an function within a cell that can compare two cells for a certain text value and if it does match than it would obtain whats in the cell within the same row. This would have to be done over multiple columns and rows.

So here is an example of the excel sheet

......... A ...... B ..... C ..... D
....... -------------------------------------
Row 1 | 1234 Jim Happy Blue
Row 2 | 1235 Jim Sad Green
Row 3 | 1236 Tom Happy Blue
Row 4 | 1237 Jim Happy Blue
Row 5 | 1238 Steph Angry Blue

What I want to do is see if Jim is in column B and Blue is in Column D. IF found it will then return what is in column A. I need to obtain what is in column A over multiple cells and put it into only 1 cell.

So it should return into the cell where the function is...

1234, 1237

Thanks for any help or guidance you can give regarding this.

[StephenR]

You can probably do this with a formula but here is VBA approach:

Sub x()
Dim rng As Range, str As String
For Each rng In Range("A1", Range("A1").End(xlDown))
    If rng.Offset(, 1) = "Jim" And rng.Offset(, 3) = "Blue" Then
        str = str & rng & ", "
    End If
Next rng
Range("E1") = Left(str, Len(str) - 2)
End Sub