Search from every row of column in a column and return row number

Originally Posted by NBVC

Your question is not clear... perhaps post a sample showing what you need.

well, in A column I have logins (every row = other login name) and in C column I have messages - long text (every row = new message), where I need to find these logins. And the return value in B column should be for example a number of line, in which the login would be found.

Please Login or Register  to view this content.

column A and column C is input and B is output like on example.. output is the only number of row where the login is located. It is complete example

It hasn't worked
I am not sure that you clearly understand me, maybe my english is very bad :P so I try it one's more:
I have a report from security scan of servers, and I inserted 2 columns A,B as you can see on example. Into first column I have added logins that I need to find in error messages that are in column C. And B column that I have inserted is for output where should be the number of row with login, that would be found in error message (column C). For example when login ROMAN is founded in 10 error messages, the number or row with login ROMAN (in our case row 2) will be in front of every error message (in column B). You know, there are thousands of messages and then when I use autofilter, I will be able to see, which login is in which couple of messages

you need to explain more clearly.

Is the columnB in your sample the exactly correct output you want?
If yes, tell us why the number should be that

yes, the example is completeted imagination.. column B on my sample is, what I really need to. And the numbers in column B are numbers of lines in which is login name. For example login ROMAN is on the second line so number 2 should be in front of every message, where login ROMAN really is.
("In front of" - because B column is in front of C column)

no because it is confidential. well.. I think that sample is good and not so hard to understand.. the point is, that I need to find login names from A column in C column and into B column I want to insert number of line, in which is login name as is on sample. I try say it one's more
function will get login name from the first line (PETER) from column A and try to find it in column C where are long messages. when it find PETER in message, it will put into B column the number of line, where PETER is located. In our case "1" because PETER is in the first line. Then function will go to the second line and get ROMAN. Then it will try to find it in whole column C and when it find ROMAN, it will put the number of line, where is ROMAN located. In our case "2" and so on.. When I will have those numbers of lines of each login name, I will be able to filter messages with autofilter and display only messages with login name that I need.
huh

Please Login or Register  to view this content.

Originally Posted by NBVC

Okay maybe this?

In B1, try:

=IF(C1="","",Match(C1,A:A,0))

OK now it does exactly what I need but it only matches logins in C column, not find. I need to find logins in message like:
Aging nicht eingeschaltet [ peter ]
But result in this case is such as I want. Now how to combine your function with SEARCH or FIND function or I don't know which...?

I hope you understand me

Originally Posted by NBVC

tRY

=MATCH(1,SEARCH("*"&$A$1:$A$10&"*",C1))

adjust range to suit...

You must confirm this formula with CTRL+SHIFT+ENTER not just ENTER.. YOu will see { } brackets appear....

Then copy it down.

good work it's worked as I wanted
but, in this format

Please Login or Register  to view this content.

when it find login, it will put the number of line above. For example in our case when it find ROMAN which is in line 2, it won't put the number 2. It will put number of line abowe, in our case 1.

Originally Posted by NBVC

Match() finds the position within the defined range...

so if your Range is (D2:D29) then D2 is position 1 and that is why you are offset by one...

Try either:

=MATCH(1;SEARCH("*"&$D$2:$D$29&"*";F2);0)+1

or

=MATCH(1;SEARCH("*"&$D$1:$D$29&"*";F2);0)

Note: I forgot to put the last argument of ,0 on my previous formula... this helps Match() find an exact match.

it works perfectly thank you