?
Solved

Excel Lookup Formula

Posted on 2012-09-10
11
Medium Priority
?
372 Views
Last Modified: 2012-09-11
I have an excel sheet with two columns.  Column A has words like Chest, Head, Legs, etc.  Column B has a 6 digit number.  

What I am struggling with is an advanced VLOOKUP formula.  I can tell it to give me the value of column B if it finds a particular string in ColumnA, but what I want is far more flexibility.

Example:

2V Chest              123456
Head Angio          234567
Small Bowel        345678
Chest Angio        456789


What I would like is to have a formula that searches for the string 'Chest' and in the results, it would give me 123456,456789.  It would return these two, because they are the two numbers that are associated with anytime the word 'Chest' appears.

Is something like this possible?
0
Comment
Question by:weklica
  • 5
  • 3
  • 3
11 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38383694
Lookup's by definition only return a single value.  

However a pivot table might give you all values by using a text string restriction such as above.  Would that work?
0
 

Author Comment

by:weklica
ID: 38383707
How do I get it to search for the string Chest and provide at least one result?  I can do the pivot portion if I can get help with string search flexibility.
Right now, I have to know the entire string or it doesn't find it.  Sometimes, Chest may be one of three words in the cell....
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38383708
Here is a small example file.  

On the first tab select the dropdown box on the row labels.  

Then type in chest in the search box and press enter.

-SA
Pivot-Table-Method.xlsx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38383715
How do you intend to use the results?  

Does that pivot table option look like it will do what you want?

-SA
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 38383749
Hi,

Here is way to achieve this, though would work for one search string at a time.

Assume your data in A2:B5

in C1

=REPT("",0)

In C2 and copied down,

=IF(ISNUMBER(SEARCH($E$2,A2)),IF(NOT(ISNUMBER(SEARCH(B2,LOOKUP("zzzz",$C$1:C1)))),LOOKUP("zzzz",$C$1:C1)&", "&B2,""),0)

In F2,

=TRIM(MID(LOOKUP("zzzzz",C1:C5),2,255))

where E2 holds the search string.

Kris
0
 

Author Closing Comment

by:weklica
ID: 38383956
WOW!  That is amazing.  Perfect Solution!  Perfect!  Thanks much.
0
 

Author Comment

by:weklica
ID: 38384375
I have just one more questions Kris.  It seems be to capped at around 30 results.  I have 4500 rows that it is searching through, but the max number of results is about 30 and it seems to consistent to be accurate.  Furthermore, I look at the bottom rows of display values in C before they go to F for cleanup, and they are same length, but different results.

What do I do to make sure there is no limit on the number of results or at least make sure that limit exceeds 100 or something?
0
 

Author Comment

by:weklica
ID: 38384384
Yes, I just confirmed that is what it is idoing.  it will find the first 29, then just replaces the last one (number 30) with a new one beyond that.
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38385369
Hi

It seems the lookup formula could only fetch upto 255 characters.

Try this UDF

Option Explicit
Dim dic     As Object
Function KCONCAT(Criteria, InputRange As Range, ConcatRange As Range, _
                    Optional Unique As Boolean = False, Optional Delim As String = ", ") As String
    
    Dim k(), IR, CR, i As Long, n As Long
    
    If TypeOf Criteria Is Range Then Criteria = Criteria.Value
    
    IR = InputRange.Value2: CR = ConcatRange.Value2
    
    Criteria = LCase(Criteria)
    
    If Unique Then
        If dic Is Nothing Then
            Set dic = CreateObject("scripting.dictionary")
                dic.comparemode = 1
        End If
    End If
    For i = 1 To UBound(IR, 1)
        If LCase(IR(i, 1)) = Criteria Then
            If Unique Then
                dic.Item(CR(i, 1)) = Empty
            Else
                n = n + 1: ReDim Preserve k(1 To n)
                k(n) = CR(i, 1)
            End If
        End If
    Next
    
    If Unique Then
        If dic.Count Then KCONCAT = Join(dic.keys, Delim)
    ElseIf n Then
        KCONCAT = Join(k, Delim)
    End If
    
End Function

Open in new window


and try

=KCONCAT(E2,A2:A1200,B2:B1200)

where

E2: search string

if you need unique values,

=KCONCAT(E2,A2:A1200,B2:B1200,TRUE)

To use this UDF,

Hit Alt+F11

Go to Insert > Module and paste this code there.

Hit Alt+Q

Kris
0
 

Author Comment

by:weklica
ID: 38388470
Would you mind if i made a 30 second video of what it is doing and send it to you?  There is a bug it seems and isn't working.  If I could email a quick video, it would likely make sense to you.  I pasted the module and see that Excel processes a bunch of data when I copy that =KCONCAT(E2,A2:A1200,B2:B1200) all the way down, but then it doesn't really work.  thanks...
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38389549
Hi

Be sure you make the range absolute while copying the formula down the way. Try

=KCONCAT(E2,$A$2:$A$1200,$B$2:$B$1200)

Kris
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

850 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