Returning the cell reference when you find a value in an array
 

Hey folks! I've scoured the Excel help documentation, and I haven't found
anything that can solve my problem here.

Say I have a table that looks like this:

A B C
1 Ed John Kim
2 Tim Joe Sue
3 Al Ann Zoe


What I need is some formula where I say, "What cell contains 'Sue'?" and it
would return "C2".

Any ideas? Thanks!

-Taylor

Returning the cell reference when you find a value in an array
 

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff

"taylor" wrote in message
...
Hey folks! I've scoured the Excel help documentation, and I haven't found
anything that can solve my problem here.

Say I have a table that looks like this:

A B C
1 Ed John Kim
2 Tim Joe Sue
3 Al Ann Zoe


What I need is some formula where I say, "What cell contains 'Sue'?" and
it
would return "C2".

Any ideas? Thanks!

-Taylor

Returning the cell reference when you find a value in an array
 

That worked *perfectly*, thank you! :)

-Taylor


"T. Valko" wrote:

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff

"taylor" wrote in message
...
Hey folks! I've scoured the Excel help documentation, and I haven't found
anything that can solve my problem here.

Say I have a table that looks like this:

A B C
1 Ed John Kim
2 Tim Joe Sue
3 Al Ann Zoe


What I need is some formula where I say, "What cell contains 'Sue'?" and
it
would return "C2".

Any ideas? Thanks!

-Taylor

Returning the cell reference when you find a value in an array
 

BTW, is there any chance you could explain how that works? I can adapt it
without understanding it, but it's always good to learn.

-Taylor

"taylor" wrote:

That worked *perfectly*, thank you! :)

-Taylor


"T. Valko" wrote:

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff

"taylor" wrote in message
...
Hey folks! I've scoured the Excel help documentation, and I haven't found
anything that can solve my problem here.

Say I have a table that looks like this:

A B C
1 Ed John Kim
2 Tim Joe Sue
3 Al Ann Zoe


What I need is some formula where I say, "What cell contains 'Sue'?" and
it
would return "C2".

Any ideas? Thanks!

-Taylor

Returning the cell reference when you find a value in an array
 

You're welcome. Thanks for the feedback!

Biff

"taylor" wrote in message
...
That worked *perfectly*, thank you! :)

-Taylor


"T. Valko" wrote:

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff

"taylor" wrote in message
...
Hey folks! I've scoured the Excel help documentation, and I haven't
found
anything that can solve my problem here.

Say I have a table that looks like this:

A B C
1 Ed John Kim
2 Tim Joe Sue
3 Al Ann Zoe


What I need is some formula where I say, "What cell contains 'Sue'?"
and
it
would return "C2".

Any ideas? Thanks!

-Taylor

Returning the cell reference when you find a value in an array
 

Let's use a slightly smaller dataset.

...........A..........B..........C..........D
1......Tim.......Joe....................Sue
2......Ann......Sue.........................

=ADDRESS(MAX((A1:B2=D1)*ROW(A1:B2)),MAX((A1:B2=D1) *COLUMN(A1:B2)),4)

The arguments to ADDRESS are as follows:

=ADDRESS(row_number,column_number,reference_style)

We use this expression to calculate the row_number:

MAX((A1:B2=D1)*ROW(A1:B2))

(A1:B2=D1) will return an array of either TRUE or FALSE, (does that cell =
Sue)

Tim=Sue = FALSE.....Joe=Sue = FALSE
Ann=Sue = FALSE....Sue=Sue = TRUE

The TRUE and FALSE is then multiplied by the row numbers in the referenced
range:

FALSE,FALSE * ROW(1) = 0,0
FALSE,TRUE * ROW(2) = 0,2

The result is an array that is passed to the MAX function:

MAX({0,0;0,2})

The result of the MAX function is 2 which is the row_number argument used by
ADDRESS.

The same process is used to obtain the col_number.

So, based on the above sample data we end up with this:

=ADDRESS(2,2,4)

The 4 in the formula is the reference_style to return. 4 means a relative
reference.

And the final result is B2

Biff

"taylor" wrote in message
...
BTW, is there any chance you could explain how that works? I can adapt it
without understanding it, but it's always good to learn.

-Taylor

"taylor" wrote:

That worked *perfectly*, thank you! :)

-Taylor


"T. Valko" wrote:

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff

"taylor" wrote in message
...
Hey folks! I've scoured the Excel help documentation, and I haven't
found
anything that can solve my problem here.

Say I have a table that looks like this:

A B C
1 Ed John Kim
2 Tim Joe Sue
3 Al Ann Zoe


What I need is some formula where I say, "What cell contains 'Sue'?"
and
it
would return "C2".

Any ideas? Thanks!

-Taylor

Returning the cell reference when you find a value in an array
 

Once again, thank you very much! Thanks to your clear and concise
explanation, I totally get it, and will be able to use these functions in the
future.

The world of array functions has opened up to me! :)

-Taylor


"T. Valko" wrote:

Let's use a slightly smaller dataset.

...........A..........B..........C..........D
1......Tim.......Joe....................Sue
2......Ann......Sue.........................

=ADDRESS(MAX((A1:B2=D1)*ROW(A1:B2)),MAX((A1:B2=D1) *COLUMN(A1:B2)),4)

The arguments to ADDRESS are as follows:

=ADDRESS(row_number,column_number,reference_style)

We use this expression to calculate the row_number:

MAX((A1:B2=D1)*ROW(A1:B2))

(A1:B2=D1) will return an array of either TRUE or FALSE, (does that cell =
Sue)

Tim=Sue = FALSE.....Joe=Sue = FALSE
Ann=Sue = FALSE....Sue=Sue = TRUE

The TRUE and FALSE is then multiplied by the row numbers in the referenced
range:

FALSE,FALSE * ROW(1) = 0,0
FALSE,TRUE * ROW(2) = 0,2

The result is an array that is passed to the MAX function:

MAX({0,0;0,2})

The result of the MAX function is 2 which is the row_number argument used by
ADDRESS.

The same process is used to obtain the col_number.

So, based on the above sample data we end up with this:

=ADDRESS(2,2,4)

The 4 in the formula is the reference_style to return. 4 means a relative
reference.

And the final result is B2

Biff

"taylor" wrote in message
...
BTW, is there any chance you could explain how that works? I can adapt it
without understanding it, but it's always good to learn.

-Taylor

"taylor" wrote:

That worked *perfectly*, thank you! :)

-Taylor


"T. Valko" wrote:

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff

"taylor" wrote in message
...
Hey folks! I've scoured the Excel help documentation, and I haven't
found
anything that can solve my problem here.

Say I have a table that looks like this:

A B C
1 Ed John Kim
2 Tim Joe Sue
3 Al Ann Zoe


What I need is some formula where I say, "What cell contains 'Sue'?"
and
it
would return "C2".

Any ideas? Thanks!

-Taylor

Returning the cell reference when you find a value in an array
 

You're welcome. Thanks for the feedback!

Biff

"taylor" wrote in message
...
Once again, thank you very much! Thanks to your clear and concise
explanation, I totally get it, and will be able to use these functions in
the
future.

The world of array functions has opened up to me! :)

-Taylor


"T. Valko" wrote:

Let's use a slightly smaller dataset.

...........A..........B..........C..........D
1......Tim.......Joe....................Sue
2......Ann......Sue.........................

=ADDRESS(MAX((A1:B2=D1)*ROW(A1:B2)),MAX((A1:B2=D1) *COLUMN(A1:B2)),4)

The arguments to ADDRESS are as follows:

=ADDRESS(row_number,column_number,reference_style)

We use this expression to calculate the row_number:

MAX((A1:B2=D1)*ROW(A1:B2))

(A1:B2=D1) will return an array of either TRUE or FALSE, (does that cell
=
Sue)

Tim=Sue = FALSE.....Joe=Sue = FALSE
Ann=Sue = FALSE....Sue=Sue = TRUE

The TRUE and FALSE is then multiplied by the row numbers in the
referenced
range:

FALSE,FALSE * ROW(1) = 0,0
FALSE,TRUE * ROW(2) = 0,2

The result is an array that is passed to the MAX function:

MAX({0,0;0,2})

The result of the MAX function is 2 which is the row_number argument used
by
ADDRESS.

The same process is used to obtain the col_number.

So, based on the above sample data we end up with this:

=ADDRESS(2,2,4)

The 4 in the formula is the reference_style to return. 4 means a relative
reference.

And the final result is B2

Biff

"taylor" wrote in message
...
BTW, is there any chance you could explain how that works? I can adapt
it
without understanding it, but it's always good to learn.

-Taylor

"taylor" wrote:

That worked *perfectly*, thank you! :)

-Taylor


"T. Valko" wrote:

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff

"taylor" wrote in message
...
Hey folks! I've scoured the Excel help documentation, and I
haven't
found
anything that can solve my problem here.

Say I have a table that looks like this:

A B C
1 Ed John Kim
2 Tim Joe Sue
3 Al Ann Zoe


What I need is some formula where I say, "What cell contains
'Sue'?"
and
it
would return "C2".

Any ideas? Thanks!

-Taylor

Returning the cell reference when you find a value in an array
 

If there are duplicates then it gets really complicated!!!

Is there a way to list all the duplicates in a column?

Thanks

"T. Valko" wrote:

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff

Returning the cell reference when you find a value in an array
 

To list the unique duplicates:

rng = A$1:A$20

Enter this array formula** in C1:

=INDEX(rng,SMALL(IF(COUNTIF(rng,rng)1,ROW(rng)-MIN(ROW(rng))+1),1))

Enter this array formula** in C2 and copy down until you get errors meaning
all unique dupes have been extracted:

=INDEX(rng,SMALL(IF((COUNTIF(rng,rng)1)*(rng<C$1 :C1),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1)))

Post back if you want an error trap.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"rlauver" wrote in message
...
If there are duplicates then it gets really complicated!!!

Is there a way to list all the duplicates in a column?

Thanks

"T. Valko" wrote:

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff

Returning the cell reference when you find a value in an array
 

Can I send you the Excel file to show to help explain what I need?

Thanks, Ron

"T. Valko" wrote:

To list the unique duplicates:

rng = A$1:A$20

Enter this array formula** in C1:

=INDEX(rng,SMALL(IF(COUNTIF(rng,rng)1,ROW(rng)-MIN(ROW(rng))+1),1))

Enter this array formula** in C2 and copy down until you get errors meaning
all unique dupes have been extracted:

=INDEX(rng,SMALL(IF((COUNTIF(rng,rng)1)*(rng<C$1 :C1),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1)))

Post back if you want an error trap.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"rlauver" wrote in message
...
If there are duplicates then it gets really complicated!!!

Is there a way to list all the duplicates in a column?

Thanks

"T. Valko" wrote:

Try this array** formula:

E1 = Sue

=ADDRESS(MAX((A1:C3=E1)*ROW(A1:C3)),MAX((A1:C3=E1) *COLUMN(A1:C3)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If there are duplicates then it gets really complicated!!!

Biff