Multiple Lookup Values

[GRobertson]

Hi,
I have a spreadsheet containing data.
Column A - Contains Supplier (e.g. BG, EON, EDF)
Column B - Contains Status (e.g. Open, Closed, Completed, Cancelled)
Column C - Contains a Date

If want a formula in Cell A1, that says for example. If Column A contains "BG" and Column B contains "Open" the cell A1 is to display the oldest date.

 

[Special-K99]

Are you checking for "Open" on the same row as "BG" or can "Open" occur ANYWHERE in column B irrespective of where "BG" is found in column A ?

 

[GRobertson]

So if my data is in rows

Column A Column B Column C
BG open 01/06/2016
EON open 01/05/2016
BG Open 01/07/2016
WOn Closed 31/01/2016

I want a formula that for example will search and display the oldest date for any record where column a equals Bg and column B equals Open. So in the example about the result should be 01/06/2016

 

[zoliidais]

Hi,

Try this one:

Option Compare Text

Sub Test()
Dim Arr As Variant
Arr = Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row)

Dim x As Integer
Dim success As Integer
Dim Result As Date

Result = Format(Result, "Short Date")

For x = 1 To UBound(Arr, 1)

    If Arr(x, 1) = "BG" And Arr(x, 2) = "Open" Then
        
            If success = 0 Then
                
                success = 1
                Result = Arr(x, 3)
                
            End If
            
            If Result > Arr(x, 3) Then Result = Arr(x, 3)
        
    End If

Next x

MsgBox Result

End Sub

Hope this helps,
Zoliidais