Returning cell references

[JaneG]

Is there a formula that returns a cell address from a
range of cells when it meets a condition? For example,
if you have a range A1:B4 and one of those 8 cells has
the desired value, can you retrieve the cell address of
that one cell via a formula?

Thanks!

 

[Bernie Deitrick]

Jane,

Using formulas, it is do-able for single row or single column references.
For blocks, such as you want, then it's easier to use a custom
User-Defined-Function. Copy this to a regular codemodule:

Function WhichMatched(inRange As Range, _
inValue As Variant) As String

Dim myCell As Range
For Each myCell In inRange
If myCell.Value = inValue Then
WhichMatched = myCell.Address(False,False)
Exit Function
End If
Next myCell

WhichMatched = "Not Found"
End Function

This would be used like

=WhichMatched(A1:B4,"Jane")

HTH,
Bernie
MS Excel MVP

 

[Jason Morin]

You could use this, where E1 holds the desired value:

=ADDRESS(MAX(ISNUMBER(FIND(E1,A1:B4))*ROW(A1:B4)),MAX
(ISNUMBER(FIND(E1,A1:B4))*COLUMN(A1:B4)),4)

Array-entered, meaning insert the formula and the while
holding down the Ctrl and Shift keys, press Enter.

Of course the desired value exists multiple times in the
range, you'll only get one cell address.

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi
for a formula approach use the following array formula (entered with
CTRL+SHIFT+ENTER):
=ADDRESS(MIN(IF(A1:B4=6,ROW(A1:B4))),MIN(IF(A1:B4=6,COLUMN(A1:B4))))

returns the cell address with the value '6'