Return multiple values using a lookup function

M

Mayo

I have looked through the threads and I have been unsuccessful in adapting
the formulas for my needs. Here is my request:

I am attempting to return mutiple matching values based on matching values.

Here is a sample set below. The duplicates are intentional because one ID #
can be in two locations and two locations will have multiple ID #s:

ID # Location 2nd ID Find Matching Location:
105802 03973 105802
111186 03205 111186
116384 03258 116384
118581 03952 118581
134777 02580 119582
135246 03198 134777
135246 03258 135246
135246 02834 138106

Let's assume that the ID # are in column B1:B9 (all columns include a
header), Locations are in column C1:C9, and the IDs' to be matched are in
column L1:L9. The ID #s in column L are not duplicated because the goal is
to match up all Locations with each ID # either by comma separated values or
otherwise.

What I'd like it to look like at the end is:

Matching Location
03973
03205
03258
03952
N/A
02580
03198,03258,02834

Please let me know if you require further information.

Thank you,
 
P

Pete_UK

Are all the columns between C and L used, or can I use one of them for
a helper column to make the formula easier?

Failing that, can I use column N (assuming column M is where you want
the results), and then hide it if necessary?

Pete
 
P

Pete_UK

Okay, while I was waiting I put this in N2:

=IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2))

and copied it down to N9. It gives this:

105802_1
111186_1
116384_1
118581_1
134777_1
135246_1
135246_2
135246_3

i.e. a sequential count tagged on the the end of the ID number. You
can hide column N if you want to.

Then I put this in M2:

=IF(ISNA(MATCH(L2,B:B,0)),"N/A",VLOOKUP(L2,B:C,2,0))&IF(COUNTIF(B:B,L2)
1,","&INDEX(C:C,MATCH(L2&"_2",N:N,0)),"")&IF(COUNTIF(B:B,L2)
2,","&INDEX(C:C,MATCH(L2&"_3",N:N,0)),"")
Click to expand...

and copied this down to M9. Notice that I've used full column
references as I suspect you have more data than just the 9 rows of
your example, and this means you don't have to adjust things. This is
what I got with your data:

2nd ID Find Matching Location:
105802 03973
111186 03205
116384 03258
118581 03952
119582 N/A
134777 02580
135246 03198,03258,02834
138106 N/A

which is what you said you wanted.

The formula just gives a maximum of up to 3 repetitions, as per your
example, but if you have more than this then post back, letting me
know how many you are likely to encounter (if it's only a few more
then the formula can be amended quite easily).

Hope this helps.

Pete
 
M

Mayo

Thanks so much! The formula does exactly what I wanted it to do. You were
correct that I have more than a possible 3 repititions. There is a
possibility of up to 32 repititions. My apologies for taking up so much
time on this issue, but could you please explain how I can adjust the formula
to account for that many reps?

Thanks.
 
P

Pete_UK

Well, with that many repetitions I'd be tempted to use a User-defined
function (UDF), as a spreadsheet formula using normal functions will
be extremely long and difficult to maintain.

If I have time later on I may look into it.

Hope this helps.

Pete
 
M

Mayo

Thanks Pete. The formula is incredible. I understand that the number of
reps could be monotanous so if you have time to look into it later on, then
that would be great.

I've never used a UDF so that would be a new experience for me. Is it
possible if the reps are only 10 or 11 long? I am thinking the formula would
still be quite lengthy.

Thanks again!
 
P

Pete_UK

If you want to play about with the formula I have given to you, this
will enable you to have up to 4 repetitions for each ID #:

=IF(ISNA(MATCH(L2,B:B,0)),"N/A",VLOOKUP(L2,B:C,2,0))
&IF(COUNTIF(B:B,L2)>1,","&INDEX(C:C,MATCH(L2&"_2",N:N,0)),"")
&IF(COUNTIF(B:B,L2)>2,","&INDEX(C:C,MATCH(L2&"_3",N:N,0)),"")
&IF(COUNTIF(B:B,L2)>3,","&INDEX(C:C,MATCH(L2&"_4",N:N,0)),"")

I've just split this manually here so that you can better understand
how it works.

The first part will return N/A if there is no match, or it will return
the first matching value (VLOOKUP and MATCH will always return the
first matching value and then stop looking).

The second part is made up of multiple IFs concatenated together -
basically, these look for the next sequential item in column N and
return "" (empty string) if there isn't one, or they return a comma
followed by the corresponding value from column C. Notice the
similarity in each of the IFs - the first has a comparison of >1, then
2, then >3 (and so on), and has a corresponding sequence number of
Click to expand...
_2, _3, _4 (and so on).

So, if you want to take this further all you need to do is highlight
this part in the formula bar:

&IF(COUNTIF(B:B,L2)>3,","&INDEX(C:C,MATCH(L2&"_4",N:N,0)),"")

then do CTRL-C (copy), then move the cursor to the end of the formula
and CTRL-V (paste) to tag a copy onto the end of the formula, and then
change the >3 and _4 to >4 and _5 respectively.

You can do this a few times, maybe to get up to 10 repetitions, and
then copy the formula down. Obviously the formula will grow in length
each time you do this.

With a UDF it is written in VBA and can perform repetitive
calculations like this through a loop, so that it will just return the
result you want. Consequently, the formula will just be something
like:

=result(...)

where result is the name of the UDF.

However, I'm a bit pushed for time at the moment, so I'll come back to
it later on.

Hope this helps.

Pete
 
M

Mayo

Pete,

You are tremendously helpful and your explanation of this formula is crystal
clear.

For now, I will continue to muck around with the formula you have given me
but if you have time later on, and you wouldn't mind helping me understand
creating a UDF, then I'd be glad to accept your help.

Based on your explanation, a UDF would make this worksheet far more
manageable than it currently is. Hope you are a patient man Pete because
this may take a bit of time for me to understand what steps I need to take.

Thank you
 
P

Pete_UK

I was tied up longer than I expected, so I haven't got chance to look
at it now. I'll pick it up in the morning if I have chance.

Pete
 
P

Pete_UK

I hope you are still monitoring this thread. I've put together a UDF
which mimics the formula that I gave you, but which will handle any
number of repeats (tested up to 32). You still need to have the
formula in N2, i.e.:

=IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2))

copied down as far as you need (the same number of entries as you have
in column B). Then, with the UDF installed (see below) you can put
this formula in M2:

=res(L2)

and just copy this down as far as you have entries in column L.

To install the UDF, do Alt-F11 to bring up the VB Editor, then click
Insert | Module, and then copy the following from this post and paste
it into the window:

Function res(my_ID) As String
Dim reps As Integer, num_reps As Integer
num_reps = Application.WorksheetFunction _
.CountIf(Range("B:B"), my_ID)
If num_reps = 0 Then
res = "N/A"
Else
res = Application.WorksheetFunction _
.VLookup(my_ID, Range("B:C"), 2, 0)
If num_reps > 1 Then
For reps = 2 To num_reps
res = res & "," & _
Application.WorksheetFunction _
.Index(Range("C:C"), _
Application.WorksheetFunction _
.Match(my_ID & "_" & reps, Range("N:N"), 0))
Next reps
End If
End If
End Function

Then just click on the Close icon to get back to the Excel window and
now you can enter the formula in M2. I've manually broken a number of
the lines in the UDF so that you don't get spurious line breaks in the
newsgroup viewer that you are using. And that's all there is to it.

When you save the file this VBA code is saved with it. This means that
when you open the file subsequently you will be warned that it
contains a Macro (assuming your security level is set to Medium).

Hope this helps.

Pete
 
M

Mayo

Excellent! I was relying on the notifications to let me know when you posted
a response and for some reason I did not receive an email. Thank you for all
the time you've put into this for me and my apologies for taking so long to
thank you.

Calay Mayo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

using form field values in a query 1
Create a Report using values form multiple combo boxes 2
DCPROMO Failed 0

Top