COUNTIF to Display Cell Reference

[MelMel1]

For reasons I cannot understand I have to use a COUNTIF function to lookup values in a range. This works just fine but I need to display a corresponding cell instead of TRUE or FALSE. I have used INDEX, MATCH, VLOOKUP, Power Pivot Relationships and this is the formula that does not produce an error. If I can get it to display a cell reference instead of TRUE or FALSE I can add another column to get the data to produce I need to see.

=COUNTIF('Archer Search Report'!H:AG,Sheet15!A2)>0

Any ideas to getting the cell reference instead of TRUE or FALSE?


Additional Info:

Essential I have two tables. Table1 has a current ID list and Table2 has a range of Legacy IDs. If an ID in Table1 matches any of the IDs in the range on Table2 there is a corresponding number which I need populate.

 

[Marcelo Branco]

Welcome to Mr Excel Forum

A small data sample (~10 rows), including Table 1 and Table 2, along with expected result(s) would be helpful for testing purposes.

M.

 

[Marcelo Branco]

To provide a data sample see section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[MelMel1]

Welcome to Mr Excel Forum

A small data sample (~10 rows), including Table 1 and Table 2, along with expected result(s) would be helpful for testing purposes.

M.

Thank you very much for your reply. Unfortunately all the links provided are blocked by my system admin. so I cannot attach per section B.

 

[Marcelo Branco]

Try Copy /Paste.
In Excel select a relevant part of Table 1 (~10rows); copy (Ctrl+C); paste (Ctrl+V) here in the forum reply page.
Do the same for Table 2
Also try to provide the expected results related to such data sample and the logic behind.
I have to leave now (i'll take a look later).
If you provide a good data sample, in the meantime maybe someone else can help you.

M.

 

[Eric W]

I'm not entirely clear on what you want, but maybe:

=ADDRESS(MIN(IF('Archer Search Report'!$H$1:$AG$100=Sheet15!A2,ROW('Archer Search Report'!$H$1:$AG$100))),MOD(MIN(IF('Archer Search Report'!$H$1:$AG$100=Sheet15!A2,ROW('Archer Search Report'!$H$1:$AG$100)*1000+COLUMN('Archer Search Report'!$H$1:$AG$100))),1000),4)

confirmed with Control+Shift+Enter.

 

[Eric W]

A bit shorter:

=CELL("address",INDIRECT(TEXT(MIN(IF('Archer Search Report'!$H$1:$AG$100=Sheet15!A2,ROW('Archer Search Report'!$H$1:$AG$100)*1000+COLUMN('Archer Search Report'!$H$1:$AG$100))),"R000C000"),0))

with CSE. But it uses INDIRECT, and I've seen some issues with CELL too.

 

[MelMel1]

A bit shorter:

=CELL("address",INDIRECT(TEXT(MIN(IF('Archer Search Report'!$H$1:$AG$100=Sheet15!A2,ROW('Archer Search Report'!$H$1:$AG$100)*1000+COLUMN('Archer Search Report'!$H$1:$AG$100))),"R000C000"),0))

with CSE. But it uses INDIRECT, and I've seen some issues with CELL too.

This worked. I can't thank you enough. And you answered it not being entirely clear on what I needed? Incredible. Thank you agiain!

 

[Eric W]

Well, I figured if it wasn't right, you'd come back and explain a bit more what you needed.

Glad to help!

 

[Marcelo Branco]

Well, I figured if it wasn't right, you'd come back and explain a bit more what you needed.

Glad to help!

Eric,

Well done!

M.