Search returning ans in terms of Rows & Columns

[Will]

Hi,

Can excel search which cells my answers belong to?

example:
A B C D E F
4 2 3 6 7 9

Lets say my ans is "3"

it will show "number 3 belong to (C,1)"

 

[Stefi]

If your answer is in A2, then
=ADDRESS(1,MATCH($A$2,A1:F1,0),4,1)

gives C1.

Regards,
Stefi

„Will†ezt írta:

 

[Bob Phillips]

In C,1 format

=SUBSTITUTE(ADDRESS(1,MATCH($A$2,A1:F1,0),2,1),"$",",")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Will]

stefi,

wat if i have a few rolls of number?

A B C D E F
1 5 6 7 8 9 10
2 11 12 13 19 20 21

i wan my ans to show; number 8 belong to D,1

I try to use ur formula but I cant get the result

 

[Stefi]

That's another case!
I could solve it only with a UDF:

Public Function FindInRng(findrng As Range, ansrng As Range) As String
FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1),
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address(False, False)
End Function

Usage (answer being in G1):

=findinrng(A1:F2,$G$1)


Adjust A1:F2 range to your needs!

Regards,
Stefi

„Will†ezt írta:

 

[Dave Peterson]

If that UDF is gonna be used in a cell on a worksheet, then it won't work until
xl2002.

..Find doesn't work in xl2k and below if used in a function called from a cell on
a worksheet.

 

[Stefi]

Then it's up to Will either to use XL2003 or find somebody who can solve this
task with worksheet functions!
Stefi


„Dave Peterson†ezt írta:

 

[Bob Phillips]

Or change the UDF

Public Function FindInRng(findrng As Range, ansrng As Range) As String
Dim oRow As Range
Dim iPos As Long
For Each oRow In findrng.Rows
On Error Resume Next
iPos = Application.Match(ansrng, oRow.Cells, 0)
On Error GoTo 0
If iPos > 0 Then
FindInRng = Application.Substitute( _
Cells(oRow.Row, iPos).Address(, False), "$", ",")
Exit For
Else
findrng = ""
End If
Next oRow
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Stefi]

Yes, it's a nice solution. The best thing should have been to ask Will what
version does he use!

Stefi


„Bob Phillips†ezt írta:

 

[Will]

I am using 2003.

Yes, it's a nice solution. The best thing should have been to ask Will what
version does he use!

Stefi


„Bob Phillips†ezt írta:

 

[Gord Dibben]

Stefi

The "best" solution is one that works across all versions, not just Will's
version.


Gord Dibben MS Excel MVP

Yes, it's a nice solution. The best thing should have been to ask Will what
version does he use!

Stefi


„Bob Phillips” ezt írta: