RE: How to return a cell reference as result of a formula

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

"Billy Liddel" wrote:

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" wrote:

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.
.

Relevant Pages

  • Re: Solving the lib mismatch problem
    ... This section is pointed to by an entry in the section table with the ... The number of entries in the table is determined by DT_VERDEFNUM. ... This is an index into the string section referenced in the section ... Offset in the string section reference by the link in the section ...
    (comp.unix.programmer)
  • Re: Bibliography unexpectedly creates duplicate entries in Current Lis
    ... Two entries are considered identical by Word if their tag is the same. ... When you try to add an entry to a 'database' Word checks if there is no entry with that tag yet in the database. ... a citation of it in your text. ... > erroneously added to the citation reference (presumably Word sees the ...
    (microsoft.public.word.docmanagement)
  • Re: Bibliography unexpectedly creates duplicate entries in Current Lis
    ... Be sure not to type another entry for the same item when you need to ... Intermittently and unexpectedly the Current List contains duplicate entries. ... erroneously added to the citation reference (presumably Word sees the ...
    (microsoft.public.word.docmanagement)
  • Re: World book software?
    ... want it to automatically recognize terms that have entries and hot ... For bonus points the main entry should ... have a list of links that go to other entries that reference the ...
    (rec.games.frp.misc)
  • Re: nested structure with "internal references"
    ... dict entries can be lists or other dicts. ... The lists and dicts can also contain int, float, string, ... ... But i'd also like to have something like a "reference" to another ... I'd like to refer to another entry and not copy that entry, ...
    (comp.lang.python)