How to return a cell reference as result of a formula

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: Questionable Results
    ... your query is very simple. ... - PAYEE_ID is a Text type field? ... Then compact the database again to get rid of this junk: ... If you are still stuck after that, and you can verify that the 3 assumptions ...
    (microsoft.public.access.queries)
  • Re: Filtering a form causes a crash
    ... I'm going to have to start a new database. ... Something in it is corrupt. ... > (I am assuming that CallerID = a Number type field. ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.formscoding)
  • Re: Opening an image from hyperlink field in forms
    ... Do not use an OLE Object type field to store these images inside the ... That will very quickly bloat your database. ... Use a Hyperlink type field, so you have a reference to the jpg file on the ...
    (microsoft.public.access.formscoding)
  • Re: SQL beginner: How to count search result "categories"
    ... I have a database of bibliographic records. ... Ideally I guess I'd like a query generic ... without any changes to the SQL. ... Assuming that the "search" is based on other fields (not the TYPE field) then ...
    (comp.databases.ms-access)
  • Re: autofill on a combo box not filling properly
    ... >I have a combo box where i store some of our product numbers for my users. ... All service packs up to date? ... in only one database, or is it reproducible in other databases? ... field in fact a Text type field or a number field? ...
    (microsoft.public.access.forms)