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

T

taylor

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
 
T

T. Valko

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
 
T

taylor

That worked *perfectly*, thank you! :)

-Taylor


T. Valko said:
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
Click to expand...
 
T

taylor

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
 
T

T. Valko

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
 
T

taylor

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

T. Valko

You're welcome. Thanks for the feedback!

Biff

taylor said:
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
Click to expand...
 
R

rlauver

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

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

Thanks
 
T

T. Valko

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
 
Top