Help Needed!!

[DellNiv]

Hi All,

Hoping there's an easy formula for this as I am having a blank moment.

So the below is a simple example of what I am trying to pull through in to column E.

I would like to look up multiple "Item Number" which are the same, sometimes theres 2/3/4 of the same items.

I would then like to pull through the content in "Item des" from what the "Currently Selling" line is into the "New Seller" line.

really hope this makes sense! I've manually filled in what I would like in Column E for reference too!


[TABLE="width: 562"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item Number [/TD]
[TD]Currently Selling[/TD]
[TD]New Seller[/TD]
[TD]Item des[/TD]
[TD]What I need to pull through[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD]01[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD]02[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD]03[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]04[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD]05[/TD]
[TD]03[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]06[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD]07[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]08[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD]09[/TD]
[TD]07[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD]10[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD]12[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]13[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]14[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance!!

Dell

 

[Eric W]

Try this in your E3 cell and drag down:

=IF(C3="Y",INDEX(D:D,AGGREGATE(15,6,ROW($B$3:$B$20)/(($A$3:$A$20=A3)*($B$3:$B$20="Y")),1)),"")

 

[DellNiv]

Thanks Eric - What if I add another twist to this and say it won't always be a Y and could be a number of letters/words instead?

 

[Eric W]

Are you talking about the B or the C column? Can we look for any non-empty cell, or do you have an actual list of allowable values to accept?

 

[DellNiv]

assuming B & C will match.

Yes can be a list for example:

Y
N
TN
JN
JJ
QO
OOO
WQQQ

 

[Eric W]

Like this?

Book1
	A	B	C	D	E	F	G
1	A	B	C	D	E		Yes Values
2	Item Number	Currently Selling	New Seller	Item des	What I need to pull through		Y
3	1	Y		1			N
4	1		JJ	2	1		TN
5	2	Y		3			JN
6	2			4			JJ
7	2		N	5	3		QO
8	3			6			OOO
9	3	OOO		7			WQQ
10	3			8
11	3		Y	9	7
12	4	Y		10
13	4			11
14	4		Y	12	10
15	4		X	13
16	4			14
Sheet12

Cell Formulas
Range	Formula
E3	=IF(ISNUMBER(MATCH(C3,$G$2:$G$9,0)),INDEX(D:D,AGGREGATE(15,6,ROW($B$3:$B$20)/(($A$3:$A$20=A3)*ISNUMBER(MATCH($B$3:$B$20,$G$2:$G$9,0))),1)),"")