Urgently need solution, too tricky for me

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
129
Office Version
  1. 2016
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.. +-%..+-£
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
ABCDEF
1ProductQtyMatch T/F*Product
2Tee230T*Shirt
3Shirt115F*Sweat
4Sweat6140F**
Sheet
 
Upvote 0
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))))}
 
Upvote 0
FDibbins, thanks. Actual it should be a less than < .

Formula in cell F2 above should read:
Code:
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))))
 
Last edited:
Upvote 0
FDibbins, thanks. Actual it should be a less than < .

Formula in cell F2 above should read:
Code:
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))))}

ABCDEFG
2Product NameUnits£Match T/FProduct nameUnits£
3Sweats2100FalseT-Shirts12200
4Shirts...etc
5
6
7

<tbody>
</tbody>


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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top