Address & Match

[Guest]

I used the following simple function to get the number of items found in a
list that is imported from the web:

{=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))}
CSE-entered (thanks Max and Harlan)

Let’s say there were 30 items found in the entire list. Is there a simple
way to get the cell address that corresponds to each item found? Does this
require a macro?

I am using the following now:
=ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000),1)

This, however, is not the correct address! Another pair of eyes on this
would be great! What am I missing?


Thanks,
Ryan---

 

[Peo Sjoblom]

Try

=ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000,0),1)


--


Regards,


Peo Sjoblom

 

[Sandy Mann]

The range in MATCH() need to be in ascending order unless the third argument
is FALSE. try

{=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000,FALSE))}

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

You can try this array formula** but it'll be slow.

Assume you have this formula in cell D1:

{=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))}

Enter this array formula** in E1 and copy down until you get blanks:

=IF(ROWS(E$1:E1)<=D$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A$7,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(E$1:E1)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Guest]

These functions worked great; thanks to all for your help. I have one more
question, how can I display the contents of a cell on another sheet? I am
trying to match the results from the function Biff gave me, which for example
may be A2136, A2147, A2158, and so on and so forth. Basically, I am trying
to get the contents of A2136, on the Import Sheet, to show up in D1 of my
Names Sheet. I tried the following:

=OFFSET('Import Sheet'!A1,Names!D1-1,0)

This seems to increase my offset, by one for each row, so by the time I am
at the end of my list, the cell reference is off by 30! I thought that I
could subtract 1 from the Row part of the function, but it doesn't seem to
work. Please help.

Regards,
Ryan--

 

[T. Valko]

Don't see why you're using OFFSET. Each address returned by the formula will
contain the value held in A$7. Are you wanting to offset that address and
return a value from a different column?

If E2 = A2136 (this is a TEXT string and not a real cell reference) to use
that as a reference you'd need to wrap it inside an INDIRECT function:

=INDIRECT("'Import Sheet'!"&E2)

Which evaluates to:

='Import Sheet'!A2136

 

[Guest]

That's it!! I've known of that function for a while now, but I have never
actually used it before. Thanks for the help T. Valko, Sandy Mann, and Peo
Sjoblom.

Cordially,
Ryan---

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

That's it!! I've known of that function for a while now, but I have never
actually used it before. Thanks for the help T. Valko, Sandy Mann, and
Peo
Sjoblom.

Cordially,
Ryan---