Urgently need solution, too tricky for me

[colinheslop1984]

Sorry the first post was messed up...

I have used match function to compare cell A:A to cell E:E, if there's a match cell D displays either true or false. Where there is a match I want the name of that product from cell A to appear on another page.

So

A........ B.....C..........D.................E............F...... G
Product....Qty...£.......Match T/F.......Product....Qty.... £
Tee..........2.....30........T............... Sweat........4..... 100
Shirt........1.....15....... .F................Pants........0..... .0
Sweat......6....140...... .F................ Shirt........7..... 180

So the function I am looking for will search column D for a match (false) and return the product name from cell A so I can then capture and compare the data on another page in the format below

A......... .B..............C...........D......E
Product.....Sales TY....Sales LY.. +-%..+-£

 

[AhoyNC]

See if this works.
Note: Using whole column references can slow things down. If your data won't go past say row 2000, then better to use a range A2:A2000 (assumes header in first row).

This is an array formula and must be entered with CTRL-SHIFT-ENTER (command-return on MAC). Copy formula down as needed.

Excel Workbook
	A	B	C	D	E	F
1	Product	Qty		Match T/F	*	Product
2	Tee	2	30	T	*	Shirt
3	Shirt	1	15	F	*	Sweat
4	Sweat	6	140	F	*	*
Sheet

 

[FDibbins]

F2 {=IF(COUNTIF($D$2:$D$4,"F")($F$2:F2),"",INDEX($A$2:$A$4,SMALL(IF($D$2:$D$4="F",ROW($D$2:$D$4)-ROW($D$2)+1),ROWS($F$2:F2))))}

I think you are missing a comma after your COUNTIF?
=IF(COUNTIF($D$2:$D$4,"F"),($F$2:F2),"",INDEX($A$2:$A$4,SMALL(IF($D$2:$D$4="F",ROW($D$2:$D$4)-ROW($D$2)+1),ROWS($F$2:F2))))}

 

[AhoyNC]

FDibbins, thanks. Actual it should be a less than < .

Formula in cell F2 above should read:

IF(COUNTIF($D$2:$D$4,"F") < ROWS($F$2:F2),"",INDEX($A$2:$A$4,SMALL(IF($D$2:$D$4="F",ROW($D$2:$D$4)-ROW($D$2)+1),ROWS($F$2:F2))))

 

[FDibbins]

I figured there was something missing

 

[colinheslop1984]

FDibbins, thanks. Actual it should be a less than < .

Formula in cell F2 above should read:

IF(COUNTIF($D$2:$D$4,"F") < ROWS($F$2:F2),"",INDEX($A$2:$A$4,SMALL(IF($D$2:$D$4="F",ROW($D$2:$D$4)-ROW($D$2)+1),ROWS($F$2:F2))))

Cheers for the help but that didnt seem to work, here is what I have inputted, have I made an error somewhere?....

{=IF(COUNTIF('Product Data Import'!$D$3:$D$500,"False")<('Product Data Import'!$E$3:E$500),"",INDEX('Product Data Import'!$A$3:$A$500,SMALL(IF('Product Data Import'!$D$3:$D$500="False",ROW('Product Data Import'!$D$3:$D$500)-ROW('Product Data Import'!$D$3)+1),ROWS('Product Data Import'!$E$3:E3))))}

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product Name[/TD]
[TD]Units[/TD]
[TD]£[/TD]
[TD]Match T/F[/TD]
[TD]Product name[/TD]
[TD]Units[/TD]
[TD]£[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sweats[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]False[/TD]
[TD]T-Shirts[/TD]
[TD]12[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Shirts...etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


When I enter it it just displays the formula rather than making the calculation. Have I inputted something wrong?

Data source is from tab titled 'Product Data Import'. The above table is the exact layout of mine.

 

[AliGW]

Did you type the curly brackets (braces)? If so, don't. This is what you need in the cell:

=IF(COUNTIF('Product Data Import'!$D$3:$D$500,"False")<('Product Data Import'!$E$3:E$500),"",INDEX('Product Data Import'!$A$3:$A$500,SMALL(IF('Product Data Import'!$D$3:$D$500="False",ROW('Product Data Import'!$D$3:$D$500)-ROW('Product Data Import'!$D$3)+1),ROWS('Product Data Import'!$E$3:E3))))

then you click on CTRL+SHIFT+ENTER to confirm it, and this will add the curly brackets for you.

If this isn't it, check if the cell is formatted as text. If it is, delete your formula, format the cell as general and then try again.

 

[colinheslop1984]

I now get #Value! error

 

[AliGW]

This means that something is not matching correctly. Use the evaluate formula function on the formulas tab to step through the calculation to see where it is failing.

 

[colinheslop1984]

Or just comes up blank after pressing ctrl+shift+enter