Solved

# Excel Lookup Formula

Posted on 2012-09-10
319 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

LVL 11

Expert Comment

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

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

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

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

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

WOW!  That is amazing.  Perfect Solution!  Perfect!  Thanks much.
0

Author Comment

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

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

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

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

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

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.