LOOKUP Return Multiple values

[SAXON10]

Hi,

How to return the multiple values using lookup and removing duplicates corresponding to the

ranges.

Any help much appreciated

DATA		OUTPUT
Category	Item		Category	ITEM
1	D		1	A
2	A			B
1	A			D
1	A		2	C
1	B		3	E
1	B		4	AAA
1	D		5	C
2	C			D
3	E			E
3	E
3	E
4	AAA
5	E
5	C
5	D

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

I could probably come up with a complicated formula for this, but a Pivot table would be much easier.

Just select your range, A2:B17 in this case. The first row must be the headers. From the Insert tab, click Pivot Table, click OK on the dialog box. Then in the PivotTable Fields box, drag Category down to the Rows box, then drag Item to the Rows box below Category.

All done, and faster than entering a formula.

If you like, you can play around a little. Drag Category to the Rows box, then Item to the Columns box, then Item also to the Values box. That will give you a unique count of each item per category.

 

[SAXON10]

Thanks for your suggestion. If you make a formula it would be more helpful for me.
Please assist me.

 

[Eric W]

Try:

	A	B	C	D	E
1	DATA			OUTPUT
2	Category	Item		Category	ITEM
3	1	D		1	A
4	2	A		1	B
5	1	A		1	D
6	1	A		2	A
7	1	B		2	C
8	1	B		3	E
9	1	D		4	AAA
10	2	C		5	C
11	3	E		5	D
12	3	E		5	E
13	3	E
14	4	AAA
15	5	E
16	5	C
17	5	D
18
19
20

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D3 {=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF($D$2:$D2,D2)<sum(SIGN(FREQUENCY(IF($A$3:$A$20=D2,MATCH($B$3:$B$20,$B$3:$B$20,0)),ROW($B$3:$B$20)-ROW($B$3)+1))</sum(),D2,INDEX($A$3:$A$20,AGGREGATE(15,6,IF(COUNTIF($D$2:$D2,$A$3:$A$20)=0,IF(MATCH($A$3:$A$17,$A$3:$A$20,0)=ROW($A$3:$A$20)-ROW($A$3)+1,ROW($A$3:$A$20)-ROW($A$3)+1)),1))),"")} E3 {=IF(D3="","",INDEX($B$3:$B$20,MATCH(0,IF($A$3:$A$20<>D3,NA(),COUNTIFS($A$3:$A$20,D3,$B$3:$B$20,"<"&$B$3:$B$20)-SUM(IF($D$3:$D3<>D3,0,COUNTIFS($A$3:$A$20,D3,$B$3:$B$20,"="&E$2:E2)))),0)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you want to hide duplicate categories in column D, you can use a Conditional Formatting rule. Select the range D2:D1000, with the rule =D2=D1, and the number format ;;; and that will do it.

 

[SAXON10]

Thanks for help and response. You are legend.

Formula E3 working perfectly. Please check the formula D3 missing somthing two places.

{=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF($D$2:$D2,D2)<sum(SIGN(FREQUENCY(IF($A$3:$A$20=D2,MATCH($B$3:$B$20,$B$3:$B$20,0)),ROW($B$3:$B$20)-ROW($B$3)+1))</sum(<sum(<font color="Green">$A$3:$A$20</sum(<font color="Blue"><font color="Red"><sum(,AGGREGATE(15,6,IF(COUNTIF($D$2:$D2,$A$3:$A$20)=0,IF(MATCH($A$3:$A$17,$A$3:$A$20,0)=ROW($A$3:$A$20)-ROW($A$3)+1,ROW($A$3:$A$20)-ROW($A$3)+1)),1)</sum()),"")}

<tbody>
</tbody>

Please help me.

 

[Eric W]

What do you mean "missing something"? Are the results missing something, or is your version of Excel not accepting the formula because of certain functions in it? If so, what version of Excel do you have? SIGN was added in 2007, and AGGREGATE in 2010.

You can rewrite the D3 formula without SIGN or AGGREGATE like this:

=IFERROR(IF(COUNTIF($D$2:$D2,D2)<sum(if(frequency(if($a$3:$a$20=d2,match($b$3:$b$20,$b$3:$b$20,0)),row($b$3:$b$20)-row($b$3)+1)>0,1)),D2,INDEX($A$3:$A$20,SMALL(IFERROR(IF(COUNTIF($D$2:$D2,$A$3:$A$20)=0,IF(MATCH($A$3:$A$20,$A$3:$A$20,0)=ROW($A$3:$A$20)-ROW($A$3)+1,ROW($A$3:$A$20)-ROW($A$3)+1)),""),1))),"")

confirmed with Control+Shift+Enter.

But this contains IFERROR, which also was created in 2007. If you have a version older than that, then this might work for you:

=IF(COUNTIF($D$2:$D2,D2)<sum(if(frequency(if($a$3:$a$17=d2,match($b$3:$b$17,$b$3:$b$17,0)),row($b$3:$b$17)-row($b$3)+1)>0,1)),D2,INDEX($A$3:$A$17,SMALL(IFERROR(IF(COUNTIF($D$2:$D2,$A$3:$A$17)=0,IF(MATCH($A$3:$A$17,$A$3:$A$17,0)=ROW($A$3:$A$17)-ROW($A$3)+1,ROW($A$3:$A$17)-ROW($A$3)+1)),""),1)))

with Control+Shift+Enter.

Also note that this version does not allow spaces in the A1:A17 range, which is why I changed all the references in the formula. Also, it will create #NUM ! errors at the bottom of the column. You may be able to get rid of those with Conditional Formatting, but I'm not sure. CF was much different in earlier versions of Excel.</sum(if(frequency(if($a$3:$a$17=d2,match($b$3:$b$17,$b$3:$b$17,0)),row($b$3:$b$17)-row($b$3)+1)></sum(if(frequency(if($a$3:$a$20=d2,match($b$3:$b$20,$b$3:$b$20,0)),row($b$3:$b$20)-row($b$3)+1)>

 

[Jali1992]

Sorry to message you on someone else’s post,

Eric, would you be able to take a look at this please.

https://www.mrexcel.com/forum/excel...-then-display-cell-reference-each-result.html

And see if you are able to create a formula to do what I am attempting?

 

[SAXON10]

Thanks for your explanation and clarification. I am using Excel 2013 version. I am trying apply your formula but I got again formula error, Please see the snapshot and sample book for your reference.

https://www.dropbox.com/s/7u0mjp5mcwwzudi/ERRPR.PNG?dl=0

https://www.dropbox.com/s/b8njgsato67v8sb/INDEX.xlsx?dl=0

 

[Eric W]

Ugh! My apologies! No matter how many times it bites me, I still forget sometimes! The bulletin board software here interprets the < and > symbols as HTML formatting codes, so if someone uses one of them in a formula (which I did!), it's possible that the formula gets messed up. You can avoid that by using a space after either of those symbols. So let's try again:

	A	B	C	D	E
1	DATA			OUTPUT
2	Category>	Item		Category	ITEM
3	1	D		1	A
4	2	A		1	B
5	1	A		1	D
6	1	A		2	A
7	1	B		2	C
8	1	B		3	E
9	1	D		4	AAA
10	2	C		5	C
11	3	E		5	D
12	3	E		5	E
13	3	E
14	4	AAA
15	5	E
16	5	C
17	5	D
18
19
20

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D3 {=IFERROR(IF(COUNTIF($D$2:$D2,D2)< SUM(SIGN(FREQUENCY(IF($A$3:$A$20=D2,MATCH($B$3:$B$20,$B$3:$B$20,0)),ROW($B$3:$B$20)-ROW($B$3)+1))),D2,INDEX($A$3:$A$20,AGGREGATE(15,6,IF(COUNTIF($D$2:$D2,$A$3:$A$20)=0,IF(MATCH($A$3:$A$20,$A$3:$A$20,0)=ROW($A$3:$A$20)-ROW($A$3)+1,ROW($A$3:$A$20)-ROW($A$3)+1)) ,1))),"")} E3 {=IF(D3="","",INDEX($B$3:$B$20,MATCH(0,IF($A$3:$A$20<>D3,NA(),COUNTIFS($A$3:$A$20,D3,$B$3:$B$20,"<"&$B$3:$B$20)-SUM(IF($D$3:$D3<>D3,0,COUNTIFS($A$3:$A$20,D3,$B$3:$B$20,"="&E$2:E2)))),0)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I'm sticking with the original formula since it's better, and since you have a version of Excel that supports it. Let me know how this works.


Edit: I copied the formulas from this post, and they both work, which they didn't when I copied from post 4.

 

[SAXON10]

Problem solved. Formula working fine.

Thanks for your continues help and support. Really appreciate it.