Solved

Excel VLookup

Posted on 2011-03-09
4
320 Views
Last Modified: 2012-05-11
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
Comment
Question by:SeeKnowledge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 22

Expert Comment

by:rspahitz
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

by:
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

by:nutsch
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

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
0
 

Author Closing Comment

by:SeeKnowledge
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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 …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question