How to return a cell reference as result of a formula

[Bruce Williams]

I have two tables of data that have been exported from a database, the 2nd is
a subset of the first. The work request ID code in the 2nd table may appear
more than once in the main data table that can be several thousand rows long.
Also, in the main table the same ID code may have different entries in the
Type field ("requested", "scheduled" or "variable").

Using an ID code in the subset table I want to find the cell reference (or
row) of an entry in the main table where the same ID code has "variable" in
the type field.

I have used COUNTIF to find which ID codes are multiples and have then used
=IF(H2>1,VLOOKUP(A2,ReqNo,4,FALSE)="Variable","x")
(H2 is the result of the COUNTIF, A2 the ID Code and ReqNo is a named range
with ID Code in col1 and Type field in col 4) to find out if the ID Code has
"variable" in the Type field. This returns True if it does and False if not.
However it does not tell me where in the main table it occurs. (I then have
to use Ctrl+F for every occurrence, or autofilter on the main table).

I have used MATCH, but that shows me the row of the first match not the
match where Type = "Variable". I have tried to wrap my formula in the
ADDRESS function but have not managed to have any success. Is there another
function that can do this?

Can anyone help? It would be much appreciated.

 

[Billy Liddel]

Bruce

Match & address will work providing that the data on table 1 is sorted.
Assuming that the IDs are in column A and column A in named Data1

=ADDRESS(MATCH($A2,Data1,0),1)

where there are more than one entry for the lookup, you can show the first
and last entries with:

=IF(COUNTIF(Data1,A3)=1,ADDRESS(MATCH(A3,Data1,0),1),ADDRESS(MATCH(A3,Data1,0),1)&", "&ADDRESS(MATCH(A3,Data1,1),1))

Regards
peter

 

[Bruce Williams]

Hi Peter,

Thanks for that it worked very well. It didn't quite give me what I wanted
in that it didn't pick out the "Variable" reference, however that was simply
overcome by using a secondary sort key to ensure that the "variable"
reference was the first in the list. It got much simpler when the list was
sorted, which is pretty obvious now!

Many thanks.