Urgently need solution, too tricky for me

colinheslop1984

Board Regular
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

Well-known Member
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*
3Shirt115F*Sweat
4Sweat6140F**
Sheet

FDibbins

Well-known Member
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

Well-known Member
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:

FDibbins

Well-known Member
I figured there was something missing

colinheslop1984

Board Regular
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))))}

 A B C D E F G 2 Product Name Units £ Match T/F Product name Units £ 3 Sweats 2 100 False T-Shirts 12 200 4 Shirts...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.

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

Board Regular
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

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

1,082,257
Messages
5,364,074
Members
400,778
Latest member

This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...