Solved

# Excel VLookup

Posted on 2011-03-09
315 Views
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
0
Question by:SeeKnowledge

LVL 22

Expert Comment

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

LVL 33

Accepted Solution

jppinto earned 500 total points
ID: 35085556
Here's an example of a post that handles a similar situation:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21711567.html

jppinto
0

LVL 39

Expert Comment

ID: 35085585
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
``````

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
0

Author Closing Comment

ID: 35086591
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…