Find a value in a table and return the cell reference

R

Rasoul Khoshravan

Would like to discuss a little bit about this function.
ROW(A1)-1 will always be zero. Why it is necessary to include this phrase?

The role of MIN function is to return a figure from an array of only this
figure and other "False". So is it possible to use MAX function, instead of
MIN? PLease give some hint in this regard.
 
M

mpaino

Hi All,
is it possible to create a function that will look for a text/value in a
table and return the cell reference? I only have dierent values in the table,
no value repeats.
For Example:

table A1:C3
aaa bbb ccc
abb abc acb
baa bba bbc

in another cell (J2) I have the value "abc" and I want a formula to return
the cell reference in the spreadsheet nest to it. I don't want the "abc" in
the formula because I want to find different values.

function(J2) returns the cell reference in the spreadsheet, B2.

Thanks a lot for the help
 
D

Domenic

Try...

=SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C3)-ROW(A1)
+1)),MATCH(J2,INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C3)-ROW(A1)+1)),0),0))),
"$","")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, since
the table contains unique values, the following should suffice...

=SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C3)-ROW(A1)
+1)),MIN(IF(A1:C3=J2,COLUMN(A1:C3)-COLUMN(A1)+1)))),"$","")

....which also needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
J

Jim Thomlinson

You can give this a try. This is a UDF so the code must be placed in a
standard code module (not a sheet or ThisWorkbook).

Public Function MyFunction(ByVal FindValue As Variant, _
SearchRange As Range) As Range
on error resume next
Set MyFunction = SearchRange.Find(What:=FindValue, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
on error Goto 0
End Function

It returns the cell that contains the value you are looking for... You can
use it in a cell like this...

=MyFunction("abc", A1:C3)

One note is that whiel the function returns the cell where the value was
found the result will just be abc since that is what that cell contains so I
am not to sure how this help you...
 
M

mpaino

It works very well, thanks a lot for the help, but in the first formula I
figured out by the second that the last "row" has to be replaced by "column".
 
V

vezerid

=ADDRESS(SUMPRODUCT((A1:C3=J1)*(ROW(A1:C3))),SUMPRODUCT((A1:C3=J1)*COLUMN(A1:C3)))

HTH
Kostis Vezerides
 
D

Domenic

Rasoul Khoshravan said:
Would like to discuss a little bit about this function.
ROW(A1)-1 will always be zero. Why it is necessary to include this phrase?
Click to expand...

This part of the formula...

MIN(IF(A1:C3=J2,ROW(A1:C3)-ROW(A1)+1))

....returns the row number in which the criteria is found, relative to
the first row of the reference.

In this case, because the data starts in Row 1, this part -ROW(A1)+1
isn't needed. But it's needed if the data starts in a row other than
the first one, or if one or more rows are inserted at row one.

For example, if A2:C4 contains the data, ROW(A2:C4) returns the
following array of numbers...

2
3
4

If ROW(A2:C4)-ROW(A2)+1 is used instead, the following array of numbers
is returned...

1
2
3

So as you can see, the added part at the end is used to return an array
of numbers starting with the number 1.
The role of MIN function is to return a figure from an array of only this
figure and other "False". So is it possible to use MAX function, instead of
MIN? PLease give some hint in this regard.
Click to expand...

If the data contains unique values, then it doesn't matter which one is
used. However, if Row 1 and Row 3 both contain the criteria, MIN will
return Row 1, whereas MAX will return Row 3.

Hope this helps!
 
M

mpaino

Domenic, the formula returned an error with row instead of column, that's why
I tried this....anyway, the formula works pretty well, thanks again.
 
D

Domenic

mpaino said:
Domenic, the formula returned an error with row instead of column, that's why
I tried this....anyway, the formula works pretty well, thanks again.
Click to expand...

The formula I posted seems fine. It shouldn't be necessary to make any
changes other than adjusting the ranges according to your data.
Changing it as you've describe could return #N/A in some cases. If
you'd like to explore this further, post the exact formula you're using.
 
Top