# Extract values based one criteria

1. ## Extract values based one criteria

Looking for formula to extract values in G2:G10 into A17:A27 if alphabet select in A13 (for example, A) matches the first letter of values in G2:G10. For example, if A was selected in A13, need formula to only extract all values that has "A" as their first letter (see F2:F10) into A17:A27 and their corresponding values.

See attached sample file.

Thanks

2. ## Re: Extract values based one criteria

Isn't this a repeat ...???

3. ## Re: Extract values based one criteria

No. I modified the file. The first post had check boxes.

Thanks

4. ## Re: Extract values based one criteria

Not in the version I looked at: it is identical except your Table is in different columns :

5. ## Re: Extract values based one criteria

Yes, identical but table setup is different.

Thanks

6. ## Re: Extract values based one criteria

in J2

=IF(F2=\$A\$13,MAX(J\$1:J1)+1,"")

Copy down

in B17

=IF(ROWS(\$1:1)>MAX(\$J:\$J),"",INDEX(H\$2:H\$10,MATCH(ROWS(\$1:1),\$J\$2:\$J\$10,0)))

in C17

=IF(ROWS(\$1:1)>MAX(\$J:\$J),"",INDEX(I\$2:I\$10,MATCH(ROWS(\$1:1),\$J\$2:\$J\$10,0)))

Custom Format B as 0;0;;@ to hide 0s in B17 onwards

These are just a minor modification to previous formulae given by Pete.

7. ## Re: Extract values based one criteria

JohnTopley: great! formula works. Need formula for A17 to copy down.

Thanks

8. ## Re: Extract values based one criteria

JohnTopley: Can you modify this formula to start at a row number versus the entire column? See sample below. =IF(ROWS(\$110:110)>MAX(\$J1:\$J10),"",INDEX(H\$2:H\$10,MATCH(ROWS(\$1:1),\$J\$2:\$J\$10,0)))

Thanks

9. ## Re: Extract values based one criteria

Column A

=IF(ROWS(\$1:1)>MAX(\$J:\$J),"",INDEX(B\$2:B\$10,MATCH(ROWS(\$1:1),\$J\$2:\$J\$10,0)))

From Pete's original post ....

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.)

ROWS(\$1:1) is not a whole column (it is a row): it is simple a counter starting at 1 and incrementing by 1 as the formula is dragged down.

As an observation: I fail to see the merit of changing the layout from the posting replied to by Pete!

10. ## Re: Extract values based one criteria

Great work JohnTopley. Works like a charm. Thanks a million.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1