Hi there!
Struggling with using the INDEX MATCH function in my spreadsheet. My formula is currently returning only the first match result (which is often a blank), and not giving me the results of subsequent matches.
This is what it looks like. Current formula being used in column X is:
=INDEX($S$3:$S$300,MATCH($W3,$Q$3:$Q$300,0))
Capture.JPG
Thanks heaps for your help in advance!
Cheers. :D
Last edited by Leaflock; 09-05-2019 at 12:55 AM.
In "W3" which criteria. If you provide / attach sample workbook its more helpful.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Hi avk, I've added a picture to my first post to make it clearer.
Welcome to the forum
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you AliGW. Sample spreadsheet attached to first post. I manually added the desired results in a new column.
TryFormula:
Please Login or Register to view this content.
UPDATE: Actually, just double-checked, this isn't quite working. This seems to only skip the first blank value, but if there are multiple blank values before a filled cell, it still returns a blank cell.
Thanks so much avk, that worked perfectly!
Very elegant solution too. You're the best. :D
Last edited by Leaflock; 09-05-2019 at 01:32 AM.
Try this:
=INDEX($S:$S,AGGREGATE(15,6,ROW($S$3:$S$8)/($S$3:$S$8<>"")/($Q$3:$Q$8=W3),1))
Or:
=LOOKUP(2,1/($Q$3:$Q$8=W3)/($S$3:$S$8<>""),$S$3:$S$8)
Formula:Please Login or Register to view this content.
Thanks Phuocam!
I ended up using this formula, and added an IFERROR. Worked perfectly.
=IFERROR(INDEX($S:$S,AGGREGATE(15,6,ROW($S$3:$S$300)/($S$3:$S$300<>"")/($Q$3:$Q$300=W3),1)),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks