Finding all possible values from vlookup rather than just the value in the closest row?

[kbrownk]

Hi,

I need something sort of like if(anyvlookup is TRUE, then return "yes", otherwise return "no".

vllookup always returns a value based on the first cell that meets the criteria of the vlookup, but, for sake of argument, if I were to change that cell so it doesn't meet the vlookup critera, then a cell further down the row would. I don't want to delete and values of course, I just want something that considers all values that meet the vlookup critera before returning an answer.

Any suggestions?

Thanks,
kbrownk

 

[Peter_SSs]

What do you mean by "if any vlookup is true"?

Could you explain a little more about what you have and what you are trying to do?

Perhaps you could make up a small set of dummy data and show us that and tell us what the expected outcome would be, and why.

 

[sarithgada86]

What about a countif? Will tell you how many, but not necessarily where...

 

[mikerickson]

This is a concatenating version of SUMIF.

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
                    
    Rem the first three argumens of ConcatIf mirror those of SUMIF
    Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
    Dim i As Long, j As Long
    
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                            stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function