PDA

View Full Version : Solved: index/match



oleg_v
07-24-2010, 11:53 PM
hi
i have a problem with the index and match function;
that if in the search column the word that i am searching repeats more that once the value that is taking belongs to the first word only without consideration to the second word.
how can i change it?

thanks

Simon Lloyd
07-25-2010, 12:54 AM
Can you supply the formula that you are using? is the match you are looking for exactly the same as the lookup value i.e no trailing or leading spaces?

Perhaps a sample workbook would be better.

mdmackillop
07-25-2010, 01:36 AM
hi
how can i change it?


Change it to do what? Find the 2nd, 3rd, last match, all matches?

oleg_v
07-25-2010, 01:46 AM
Hi
i attached a file
i need to find all the matches but
one after another first,second and so on.
please see in the file sheet1 is the data from witch i pull the data and sheet2 is the sheet to witch i pull the data.

mdmackillop
07-25-2010, 02:04 AM
You need to use a FindNext routine, which has a good example in VBA Help. Post back with your code if you get stuck.
Please rememer, we are here to assist, not to to your work.

Edit.
A desired output is useful in such questions.

oleg_v
07-25-2010, 02:09 AM
hi
i do not need the vba code i am just need is the nelp how to change the function line that i use :
=ABS(IF(ISNUMBER(SEARCH("t",A2)),INDEX(Sheet1!$C:$C,MATCH
(A2,Sheet1!$B:$B,0)+4),INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+2)))

Aussiebear
07-25-2010, 02:18 AM
Have you had a look at the FindNext function in VBA Help?

mdmackillop
07-25-2010, 02:20 AM
What is the relevance of SEARCH("T",A2) and ABS? You have no data in Column C.
It looks as if you have made no attempt to create your own solution.

oleg_v
07-25-2010, 03:32 AM
hi the file that i posted is a sample.

se aatached my original file and yes i made a several attempts to make it work.
see attache original file.

Simon Lloyd
07-25-2010, 03:47 AM
As you haven't explained where you want the formual, how many cells you want to populate i have no choice, even though you say you don't want a VBA solution, but to provide a crude bit of code that you can step through and see what is happening and maybe adapt it!Sub Crude_Find()
Dim IB As String, Strt As Range
IB = Application.InputBox("Enter the match to be found", "Find all matches")
Set Strt = Cells.Find(What:=IB, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
Do
Cells.FindNext(After:=ActiveCell).Activate
msg = msg & ActiveCell.Address & vbLf
Loop Until Strt.Address = ActiveCell.Address
MsgBox "matches found at these addresses" & vbLf & msg, vbOKOnly, "Found matches"
End Sub

oleg_v
07-25-2010, 04:21 AM
thanks for all your help

Oleg

Mikey
07-25-2010, 03:50 PM
Hello Oleg,

If you want a formula solution try this array formula in B2

=ABS(INDEX(Sheet1!C$1:C$2000,SMALL(IF(Sheet1!$B$1:$B$2000=$A2,ROW(Sheet1!$B $1:$B$2000)-ROW(Sheet1!$B$1)+1),COUNTIF($A$2:$A2,$A2))+IF(COUNTIF($A2,"*t*"),4,2)))

Confirm with CTRL+SHIFT+ENTER and then copied down and across

regards, Mikey

oleg_v
07-26-2010, 12:13 AM
hi thanks it works greate

please be so kind and explain the formula this is very important lesson for me


thanks