Hi,
I have a sheet which uses a search function. When a value is entered in to the "Search" sheet in C4, the formula in C9 is:
which works fine.
Please Login or Register to view this content.
the thing is, there could be more than one instance of the value in C4 and I want to get the next instance and put this formula into C11 and again into C13 for the next instance.
Can anyone help please?
MATCH/INDEX like VLOOKUP works great if you only need is to retrieve the first instance of the match. To retrieve all the matches we can use a sequential match table (beginning each match search in the cell after the previous match is found)
See the attached for a very simple example.
Gary's Student
hi boylers75. would you mind not merging the cells in the 3 yellow cells? i have attached a solution of mine using array formulas. and they dont work well with merged cells. to obtain the effect like merged cells, select the cells you want to merge, right-click-> format cells -> Alignment -> Horizontal: Center Across Selection -> OK
the array formulas i used must be confirmed with CTRL + SHIFT + ENTER if you ever need to edit anything.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
You can use this ARRAY formula for this, but you can not use an Array formula in merged cells.
Please Login or Register to view this content.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
try the below array formula:
A9 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),1)),"") ---- CRTL+SHIFT+ENTER
A11 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),2)),"") ---- CRTL+SHIFT+ENTER
A13 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),3)),"") ---- CRTL+SHIFT+ENTER
try the below array formula:
A9 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),1)),"") ---- CRTL+SHIFT+ENTER
A11 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),2)),"") ---- CRTL+SHIFT+ENTER
A13 =IFERROR(INDEX(STS!A:A,SMALL(IF(STS!C:C=$C$4,ROW(INDIRECT("1:"&ROWS(STS!A:A)))),3)),"") ---- CRTL+SHIFT+ENTER
You can't use that original formula to do that, but since you're using VBA anyway, you can use a Do Loop to find all the instances of that code and insert them into the results.
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks for all the help and suggestions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks