Vlookup expert needed 3

[andyjsav]

Hello
Can anyone help with this problem
The value I'm looking up may have more than one entry in the table array i'm searching, and I want excel to return all the relevant entries in the array, but excel just returns the one, presumably the first one it finds, anyone know of a way round this, perhaps there's some way of having another column(s) that does another vlookup but picks the next relevant value?

Thanks - Andy

 

[xlbo]

VLOOKUP will only ever return the 1st value.
If you want totals, you can use SUMIF but being as you say you want all possibilities returned in an array, I can see no option but to turn to a UDF (User Defined Function)
This will need VBA so please let me know if this is ok before I look at creating a solution

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[andyjsav]

Hi Geoff
Thanks for looking at this, sumif is no good as I need to see each individual value,the values being returned are dates anyway so if you are able to find a solution then it would be much appreciated

Thanks - Andrew

 

[xlbo]

This should work for you:
Function GetAllValues(rng As Range)
Dim lkupVal As String, TempStore As String
TempStore = ""
lkupVal = rng.Text
With Range("Table&quot
Set c = .Find(lkupVal, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
TempStore = TempStore & c.Offset(0, 1).Text & ","
Set c = Range("Table&quot.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Else
GetAllValues = &quot;No Matches&quot;
End If
End With
GetAllValues = Left(TempStore, Len(TempStore) - 1)
End Function

copy and paste into a standard module and then enter
=GetAllValues(A1)
where the loookup value is in A1

Your column to look in should either be named &quot;Table&quot; or you should change range(&quot;table&quot to range(&quot;C2:C500&quot or whatever the lookup range is

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DeLaMartre]

Great Stuff, as usual, Geoff! This is going to be very useful. Here's another Star.

Thanks very much!

-Bob in California

 

[MeGustaXL]

Astounding & simple! You never cease to amaze me Geoff!

Star from me, too


Chris

Varium et mutabile semper Excel

 

[KenWright]

You can also do this with an array formula.

Assume you have your list of data in A3:A20, with the associated values in B3:B20. Further assume that the entry you are looking to return all corresponding values for is in D3, and that you wish to return all matching values into the range beginning E3. The range from E3 can obviously be potentially as large as your original list of values, so you need to cater for all these.

Select the range E3:E20 and type (or paste) the following formula in:-

=INDEX(A3:B20,SMALL(IF(A3:A20=D3,ROW(A3:A20)-ROW(A3)+1,ROW(A20)+1),ROW(INDIRECT(&quot;1:&quot;&COUNTA(A3:A20)))),2)

and then array enter it using CTRL+SHIFT+ENTER.

This will give you a list of all matching entries in the order that they appear in the original list. You will also see error messages for every cell that doesn't have a valid entry in it, eg, if there were 5 matches in your 18 numbers, then you would see all 5 matches, and then 13 error messages.

These error messages can then be gotten rid of using the usual =IF(ISERROR(Your_Formula),&quot;&quot;,Your_Formula)

In this example, that would be as follows:-

=IF(ISERROR(INDEX(A3:B20,SMALL(IF(A3:A20=D3,ROW(A3:A20)-ROW(A3)+1,ROW(A20)+1),ROW(INDIRECT(&quot;1:&quot;&COUNTA(A3:A20)))),2)),&quot;&quot;,INDEX(A3:B20,SMALL(IF(A3:A20=D3,ROW(A3:A20)-ROW(A3)+1,ROW(A20)+1),ROW(INDIRECT(&quot;1:&quot;&COUNTA(A3:A20)))),2))

This will ensure that you only get matches where matches exist, and blanks elsewhere.

Formula based on one from Chip Pearson that allows you to return a specified instance of a match (eg 3rd matching value), but tweaked to allow the multiple matches, and also to get rid of the errors.

Regards
Ken...........................





----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[xlbo]

Just an FYI - I'm experiencing some issues with the FINDNEXT statement in the UDF I posted ie it doesn't seem to work in some workbooks (but does in others ??? go figure)
Anyway, the udf can be re-written as follows - it uses a loop so won't calc as quick but will work all the time:

Function GetAllValues(rng As Range)
Dim lkupVal As String, TempStore As String
TempStore = &quot;&quot;
lkupVal = rng.Text
For i = 1 To [mTable].Rows.Count
If

.Cells(i, 1).Text = lkupVal Then TempStore = TempStore &

.Cells(i, 1).Offset(0, 1).Text & &quot;,&quot;
Next i
GetAllValues = Left(TempStore, Len(TempStore) - 1)
End Function

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Zathras]

Andy, you might want to take a look at my VLOOKUPNEXT function in thread68-617422. It may be exactly what you are looking for.