# Lookup and return multiple results

1. ## Lookup and return multiple results

Hi!

I am struggling to determine a formula to return multiple results based on a lookup. The only examples I can find are not quite what I'm looking for. Two columns of data as the source data. I would try and explain what I'm looking for, but the sheet I have attached may give a better description. Source table on the left, desired result on the right. Let me know if I can help describe it.

Thank you!!!  Register To Reply

2. ## Re: Lookup and return multiple results

Here you can find 2 options, most likely you will use the UDF:https://www.get-digital-help.com/201...into-one-cell/  Register To Reply

3. ## Re: Lookup and return multiple results

This can be done with helper column, is it ok for you  Register To Reply

4. ## Re: Lookup and return multiple results

I am not sure that have you got TEXTJOIN so use this: ``Please Login or Register  to view this content.``
paste this VBA into module and then enjoy like below:

A
B
C
D
2
APACHE06
1
APACHE06 1,2
3
APACHE06
1
APACHE07 100389-1,100389-2,100389-3
4
APACHE06
1
APACHE08 47771-1,47771-2

Excel 2016 (Windows) 32 bit
C
D
1
WP ID Tank
2
APACHE06 =TEXTJOINsub(IF(\$A\$2:\$A\$70=C2,\$B\$2:\$B\$70,""),",",1)
3
APACHE07 =TEXTJOINsub(IF(\$A\$2:\$A\$70=C3,\$B\$2:\$B\$70,""),",",1)
 Sheet: Sheet 1

Formula have to be accept with Ctrl+Shift+Enter (not just Enter) as is array formula.  Register To Reply

5. ## Re: Lookup and return multiple results

As you can see, there are a number of ways to achieve this. In my suggestion I use two helper columns (C and D), with this formula in C2:

=IF(A2="","-",IF(COUNTIF(A\$2:A2,A2)=1,MAX(C\$1:C1)+1,"-"))

and this one in D2:

=IF(A2="","-",IF(COUNTIFS(A\$2:A2,A2,B\$2:B2,B2)=1,A2&"_"&COUNTIF(D\$1:D1,A2&"*")+1,"-"))

These two formulae should be copied down to the bottom of your data or beyond, as can be seen by the hyphens.

To get the data in the form you require, you can then use this formula in G2:

=IFERROR(INDEX(A:A,MATCH(ROWS(\$1:1),C:C,0)),"")

and this one in H2:

=IF(G2="","",INDEX(B:B,MATCH(G2&"_1",D:D,0)) & IFERROR(", "&INDEX(B:B,MATCH(G2&"_2",D:D,0)),"") & IFERROR(", "&INDEX(B:B,MATCH(G2&"_3",D:D,0)),"") & IFERROR(", "&INDEX(B:B,MATCH(G2&"_4",D:D,0)),"") & IFERROR(", "&INDEX(B:B,MATCH(G2&"_5",D:D,0)),""))

This one might look a bit daunting, but if I put manual line-breaks in it you can see it more clearly:

=IF(G2="","",INDEX(B:B,MATCH(G2&"_1",D:D,0))
& IFERROR(", "&INDEX(B:B,MATCH(G2&"_2",D:D,0)),"")
& IFERROR(", "&INDEX(B:B,MATCH(G2&"_3",D:D,0)),"")
& IFERROR(", "&INDEX(B:B,MATCH(G2&"_4",D:D,0)),"")
& IFERROR(", "&INDEX(B:B,MATCH(G2&"_5",D:D,0)),""))

This will return up to 5 items from column B (the most you have in your example data is 4), but it is easy to extend it to more by adding extra terms inside the final bracket and changing the coloured number as appropriate.

The two formula in G2:H2 can be copied down until you start to get blanks. The attached file shows this in action, and I have moved your Desired results to the right so that you can compare with the formulae-derived results directly. All the formulae are shown in blue.

Hope this helps.

Pete  Register To Reply

6. ## Re: Lookup and return multiple results

C2
=IF(B2=B1,"",B2&IFERROR(", "&INDEX(C3:C\$70,MATCH(1,INDEX((A3:A\$70=A2)*(C3:C\$70<>""),),)),""))

H2
=VLOOKUP(G2,\$A\$2:\$C\$70,3,0)  Register To Reply