I'll try to explain this. hopefully the attachment below will help. I am trying to create a list of all instances where contents in A3 is found in C5:C12 and return the values in D5:D12 without any spaces. Right now I can do it in two steps but I'd like to clean it up and do it with only one formula. Any takers??
There's nothing in A3, but I assume you mean A in general.
Use this array formula:
=IFERROR(INDEX($C$8:$C$18,SMALL(IF($A$8:$A$18="FULL",ROW($C$1:$C$11)-ROW(A$1)+1),ROW($A1))),"")
This formula is an array formula confirmed with Ctrl+Shift+Enter to exit the cell, instead of just hitting Enter.
Then drag down.
This lists in order just the entries where A = FULL. You can change FULL to a reference such as A3 or whatever.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Oops, I think I put in the wrong attachment. This one should make more more sense.
This is hardly a Tip or a Tutorial, I will move it to a better forum
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Using your second upload, this formula, similar to that of daffodil11's formula will work.
Array enter this in F5 and copy down:
Formula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Try this...Originally Posted by sabin348
Data Range
This formula entered in F5:
=IFERROR(INDEX(D:D,AGGREGATE(15,6,1/(C$5:C$12=A$3)*ROW(C$5:C$12),ROWS(F$5:F5))),"")
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks a ton guys, it worked great!
You're welcome. We appreciate the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks