searching in several columns
 

Hello,

I want to find the rownumber where three coulumns match a predefined
value. Say column a must be x, culumn b must be y and column c must be
z. I could create a loop testing columns a,b and c in every row till I
find the wanted data, but that's very slow. Alternatively I could
create an extra column d with labels made up from the data in the
columns a b and c. And use the find method to find xyz in this column.
This doesn't seem very efficient to me either.

Does any-one know a more efficient way to find the wanted data?


Wilco

searching in several columns
 

=MATCH("abc",A1:A100&B1:B100&C1:C100),0)
ctrl/shift enter the above, and if it's an error, there are no abc values in
the same row, and if it returns a number, it's the row # where it occurs.

wrote in message
oups.com...
Hello,

I want to find the rownumber where three coulumns match a predefined
value. Say column a must be x, culumn b must be y and column c must be
z. I could create a loop testing columns a,b and c in every row till I
find the wanted data, but that's very slow. Alternatively I could
create an extra column d with labels made up from the data in the
columns a b and c. And use the find method to find xyz in this column.
This doesn't seem very efficient to me either.

Does any-one know a more efficient way to find the wanted data?


Wilco

searching in several columns
 

Do you want the relative row number (nth row of your table) or the actual
Excel row number? If the worksheet row number, you can add to Bob's formula:

=INDEX(ROW(A1:A100),MATCH("abc",A1:A100&B1:B100&C1 :C100),0))



" wrote:

Hello,

I want to find the rownumber where three coulumns match a predefined
value. Say column a must be x, culumn b must be y and column c must be
z. I could create a loop testing columns a,b and c in every row till I
find the wanted data, but that's very slow. Alternatively I could
create an extra column d with labels made up from the data in the
columns a b and c. And use the find method to find xyz in this column.
This doesn't seem very efficient to me either.

Does any-one know a more efficient way to find the wanted data?


Wilco

searching in several columns
 

Maybe I misunderstand the statement, but this looks to me like it is
matching 1 value in three columns. If I'm wrong, can you please explain
how it works?

Maybe too my example was abstract and not clear enough so I'll try
again. I have a sheet with several columns. I have a.o colums: month,
year, customer, no of consults etc. Now I want to find the row where
the month, year and customer match certain values in order to find how
many consults the customer made in that particular month. How do I find
the row with this particular information? How do I find efficiently the
row where year matches 2005, month matches March and customer matches
John Doe?


Kind regards
Wilco

searching in several columns
 

The one value that Bob is matching is a concatenation of your 3 criteria.
Instead of "abc" you would use:

"March2005John Doe" or cell references to your criteria:

D1&E1&F1 (where D1=March, E1=2005, and F1=John Doe)


" wrote:

Maybe I misunderstand the statement, but this looks to me like it is
matching 1 value in three columns. If I'm wrong, can you please explain
how it works?

Maybe too my example was abstract and not clear enough so I'll try
again. I have a sheet with several columns. I have a.o colums: month,
year, customer, no of consults etc. Now I want to find the row where
the month, year and customer match certain values in order to find how
many consults the customer made in that particular month. How do I find
the row with this particular information? How do I find efficiently the
row where year matches 2005, month matches March and customer matches
John Doe?


Kind regards
Wilco

searching in several columns
 

Ok, clear! Thanks a lot for the input.

Wilco