Extract values

[bjnockle]

Looking for formula to extract values if criteria is met. There is one drop down. Letter drop-down list. Looking for a formula that will extract values in table A1:C10 if the criteria is met.

For example, if you select letter A from the drop-down in A13 , values extracted should be:

See attached file. Extract.Fruits.File.answer.1.xlsx

Please Login or Register  to view this content.

[Pete_UK]

In the attached file I put zero in D1 and this formula in D2:

=IF(LEFT(A2)=$A$13,MAX(D$1:D1)+1,"")

which is copied down to D10 (and coloured blue).

Then I used this formula in A17:

=IF(ROWS($1:1)>MAX($D:$D),"",INDEX(A$2:A$10,MATCH(ROWS($1:1),$D$2:$D$10,0)))

which can be copied across and down the tan area. I've used Conditional Formatting on cells A18:B27 with a formula of =A18=A17 to change the foreground colour to the background colour, to effectively hide the values that are the same as the row above (i.e. Apples, Antioxidants etc.)

Just change the value in the drop-down to see it all in action.

Hope this helps.

Pete

[protonLeah]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.
http://www.excelforum.com/showthread.php?t=1131443 [SOLVED???]
Thread Closed.

[bjnockle]

Pete_UK: works like a charm. I modified the file to achieve the desired outcome