Solved

# Excel Lookup Formula

Posted on 2012-09-10
Medium Priority
372 Views
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
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
Question by:weklica
• 5
• 3
• 3

LVL 11

Expert Comment

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

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

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

LVL 11

Expert Comment

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

krishnakrkc earned 2000 total points
ID: 38383749
Hi,

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

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

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

Author Comment

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

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

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
``````

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

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

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

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ā¦