Help Please

[shellie78]

I am doing financial modelling and require to do a VLOOKUP to an area and get a match to a locoation. This location has a date in another column that is in the array area. I am able to do this but once it has found all the matching locations I then wish it to populate the cell with the earliest lcation date and I do not know how to do this after I have asked it to look for the location.

So say the area is C1:H13 and the location is in B100 The location is in C column and dates in H column. I do =IF(ISERROR(VLOOKUP(B100,C1:H13,1,FALSE)),"",VLOOKUP(B100,C1:H13,2,FALSE)). This would give me a date if there was only one matching location but as there is more than one matching location I require the earliest date ie Aug 15 if that was the earliest so what is the next command?

Any help would be greatly apprecaited.

 

[NeonRedSharpie]

Function MINIF(vals As Range, rng As Range, criteria As Range) As Variant

check = 100000000


For Each cell In rng
    If cell.Value = criteria.Value And vals(cell.Row) < check Then
        Debug.Print cell.Row
        Debug.Print vals(cell.Row)
        MINIF = vals(cell.Row)
        check = MINIF
    End If
        
Next


End Function

I have this UDF I wrote the other day that should help you out.

=MINIF([date_range],[location_range],[location])

 

[RoryA]

You'll need an array formula unless your data is properly sorted:

=MIN(IF(C1:C13=B100,H1:H13))
entered with Ctrl+Shift+Enter.