asked on

Excel VLookup

if there are several cases that are true down the entire column of the VLookup formula how do you make the VLookup continue (like in a loop) and return values for all true cases

rspahitz

I typically insert a new column before the data that merges the pieces (=A1&B1) then apply the vlookup to that column.

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nutsch

You need a user-defined function for that, i.e. a macro-type function. Here is one that I believe Saurabh wrote:

Public Function myVLookup(value As Variant, rng As Range, retCol As String, Optional blAbs As Boolean = True) As String
Dim cl As Range, str As String
Dim intCol As Integer

If IsNumeric(retCol) Then intCol = CInt(retCol) Else intCol = Range(retCol & "1").Column

If blAbs = False Then 'returns the relative column
    
    For Each cl In rng
    
        If cl = value Then str = str & cl.Offset(0, intCol) & ", "
        
    Next

Else 'returns the absolute row (good for titles)
 
    For Each cl In rng
    
        If cl = value Then str = str & Cells(cl.Row, intCol) & ", "
        
    Next

End If

If Len(str) > 0 Then myVLookup = Left(str, Len(str) - 2) Else myVLookup = ""

End Function

Open in new window

Put it in a module of your worksheet and call it =myvlookup(a1,b1:b10,5)

where
a1 is the value sought
B1:B10 is the column you're looking in
5 is the absolute column you want returned (in this case 5="E")

You can add a false parameter, and then the column parameter becomes the offset from the column you're looking in, e.g.
=myvlookup(a1,b1:b10,1,false)

in which case it returns 1 column right of B1:B10, ie column C.

Thomas

SeeKnowledge

ASKER

Thanks for the tip, followed the link the response from Brad was excellent referring to the following link
http://www.cpearson.com/excel/lookups.htm#LeftLookup