Hello all,
My database project is nearly done thanks to a ton of help from this forum. Only a few issues remain. I am wondering how I can use a formula to index match the nth occurrence. Or just return the nth occurrence anyway. I have attached a file where I am using index match and getting the first occurrence.
Thanks,
sdl2Index_Match.xlsb
I'm not sure that you can find a specific number of an instance with a formula but you could use a macro. Now as of right now the macro looks for a sheet named "Sheet1" to find the L1 and P2 values and only looks nth occurrence in column A on the active sheet. So as of right now the macro is limited to those specific but they can be changed to meet your ranges of cells. Change the macro around to met your specifics or if you want me to do it upload your full sheet with all the data and ranges on it and I will modify the macro. Let me know whether it works for what you desired either way. Thanks.
If you haven't used a macro before just let me know and I can walk you through it or upload your full sheet tell me what button / hotkey you want it setup on and I will do that for you.
Please Login or Register to view this content.
Just in case you haven't used a macro before press and hold ALT + F11 to open VBA editor, Insert module, past
Thank those who have helped you by clicking the Star * below their name and please mark your post [SOLVED] if it has been answered satisfactorily.
Hi jessebranum777,
Thank you for your response. I am familiar with VBA, I was really trying to avoid it to be honest. I have this formula looking up different columns about 20 different ways. VBA seemed hard to use for this... I tried it out and its coming back nth not found with a message box that can not be closed
Last edited by sdl2; 11-10-2014 at 04:57 PM.
I will do some more research then because I love finding ways around things. I'll be back.
Ah see if this link helps
http://excel.tips.net/T003324_Findin...Character.html
Maybe this is what you are looking for:
<---------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
maybe
=INDEX(Sheet2!R1:R20,MATCH(LARGE(INDEX((Sheet2!A1:A20=P2)*(1/ROW(A1:A20)),0),L1),INDEX((Sheet2!A1:A20=P2)*(1/ROW(A1:A20)),0),0))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Now that is a creative idea. Points for you for creativity and helping me keep something on deposit for ideas for the future. Nice.Originally Posted by newdoverman
Thank you for the feedback.
Hi Guru,
I was wondering if you could also help me with the same formula?
I've similar situation but return value (answer) is descriptive (TEXT).
I've enclosed sample workbook if you could look into the same and assist thanks!
P.S. sorry forgot to attach
Last edited by MYEM1983; 05-21-2018 at 03:57 AM.
Please start a new thread of your own.
Last edited by newdoverman; 05-21-2018 at 08:28 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks