Find multiple value in columns

[Rabbit1980]

Hi.
I need your help. It is possible to find the row number of a value in column if in the column are more same values? And set up which I need to find?

I try everything but still no result. I use MS excel 2003 and I need functions. No VBA.

I.g.:

Lookup value: "a"
Match nbr.: 2
Row nbr.: ?

Column 1 values: a, b, c, a, g, h, a, t, z


Thank yooooou

 

[mikerickson]

Try this CSE formula
=SMALL(IF(A1:A100="a",ROW(A1:A100)), 2)

Which should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Aladin Akyurek]

Hi.
I need your help. It is possible to find the row number of a value in column if in the column are more same values? And set up which I need to find?

I try everything but still no result. I use MS excel 2003 and I need functions. No VBA.

I.g.:

Lookup value: "a"
Match nbr.: 2
Row nbr.: ?

Column 1 values: a, b, c, a, g, h, a, t, z


Thank yooooou

Control+shift+enter, not just enter:

=SMALL(IF(A2:A10="a",ROW(A2:A10)-ROW(A2)+1),2)

If you want the native row number...

=SMALL(IF(A2:A10="a",ROW(A2:A10)),2)

 

[Rabbit1980]

Than you very much. This is what I need. Thank you

 

[Rabbit1980]

And if I want to apply this functions to the other column than

=IF($X$1=1;SMALL(IF(A2:A10="a",ROW(A2:A10)-ROW(A2)+1),2);IF($X$1=2;SMALL(IF(B2:B10="a",ROW(B2:B10)-ROW(B2)+1),2)))

where X1 is the column number, in this case Column1 and Column2; or there is other way?

 

[Aladin Akyurek]

And if I want to apply this functions to the other column than

=IF($X$1=1;SMALL(IF(A2:A10="a",ROW(A2:A10)-ROW(A2)+1),2);IF($X$1=2;SMALL(IF(B2:B10="a",ROW(B2:B10)-ROW(B2)+1),2)))

where X1 is the column number, in this case Column1 and Column2; or there is other way?

X1: 1
Y1: 2

X2: Control+shift+enter, not just enter, and copy across:

=SMALL(IF($A$2:$A$10="a",ROW($A$2:$A$10)-ROW($A$2)+1),X$1)

Is this what you have in mind?

 

[Rabbit1980]

A Column B Column C Column D Column
Find value Which match Column nbr. Row nbr.
a 2 1 ?


I mean that I manualy insert which value, match and in which column find the value and I need the row number.

=SMALL(IF(A5:A100=$A$1;ROW(A5:A100)-ROW(A5)+1);$B$2)

how to insert in which column search for value?

Or is it possible to change array (A5:A100) in functions??
For exsample: IF(E1=1;(A5:A100);IF(E1=2;(B5:B100);""))

 

[Aladin Akyurek]

A Column B Column C Column D Column
Find value Which match Column nbr. Row nbr.
a 2 1 ?


I mean that I manualy insert which value, match and in which column find the value and I need the row number.

=SMALL(IF(A5:A100=$A$1;ROW(A5:A100)-ROW(A5)+1);$B$2)

how to insert in which column search for value?

Or is it possible to change array (A5:A100) in functions??
For exsample: IF(E1=1;(A5:A100);IF(E1=2;(B5:B100);""))

E1: a (criterion value)
E2: 2 (instance number)
E3: 1 (column number)

Control+shift+enter, not just enter:

=SMALL(IF(INDEX($A$5:$D$100,0,E$3)=E$1,ROW($A$5:$A$100)-ROW($A$5)+1),E$2)

 

[Rabbit1980]

Thank you very very much.

I have one more question.

It is possible to change the color and "Bold type" of value based on another value?
Condition formation is just for three condition. But I need more.

I mean that I set the 5 or more values form, and based on their value, with vlookup function will changed other values in columns??


than you

 

[Aladin Akyurek]

Thank you very very much.

I have one more question.

It is possible to change the color and "Bold type" of value based on another value?...

Formulas do not do that type of things. I think there are some threads which show how to use VBA to expand CF beyond 3 conditions. Try a search on the VBA option...