I have attached an example of what I am trying to do.
So the problem is, the smallest 2 numbers returns the same value so for the Person, it returns only the first result twice for those two cells.
How do I set it to display both Sandy and Maple? And is it possible for all the formulas to be set universally to expect ties?
Last edited by LJenny; 10-09-2015 at 03:54 PM.
Can you upload an example worksheet? (Go Advanced>Manage Attachments)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Can you post a SMALL sample file and show us what result you expect?
A SMALL file will have about 20 rows worth of data.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Originally Posted by ChemistB
I have attached a sample file. ^^
Try array entering this formula in C11 and fill down.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
The formula you used is great for single matches. The MATCH function returns only the first match it finds in cases like this, and that's all unfortunately.
Dave
Hi thanks so much!
This formula works on my sample spreadsheet but when I extrapolate the formula to my actual spreadsheet, it is returning the same value :S
Make sure calculation is set to Automatic.
Yup it is set to automatic.
Can you post a small sample file that demonstrates your problem?
20 rows worth of data is plenty.
Ah k, here's a better sample.
So on the summary worksheet, that's what I am trying to do.
This array formula** entered in C4 and copied down:
=INDEX('Validation Data'!A:A,SMALL(IF('Validation Data'!G$3:G$50=A4,ROW('Validation Data'!G$3:G$50)),COUNTIF(A$4:A4,A4)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Awesome! This works perfectly! Thanks!!
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
Marked as solved
Oh quick question, one of the results is coming up at #NUM, any idea why?
Last edited by LJenny; 10-13-2015 at 11:02 AM.
That usually means there is not an nth instance of the lookup value.
Typically, we would use an error trap to prevent the error.
In Excel 2003, that formula would be:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX('Validation Data'!A:A,SMALL(IF('Validation Data'!G$3:G$50=A4,ROW('Validation Data'!G$3:G$50)),COUNTIF(A$4:A4,A4)))))
Still array entered.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks