Passing a function as a range value

[ammarkr]

Hello. Although this is my first post, I'd already like to thank the board members for all their help, since this has been my go-to site for learning about Excel code from scratch.

I've finally had to create an account and post since I've become stuck, and no amount of googling has helped. Basically, I want to know how to pass a function as a range value.

To explain, consider the following table:

ColumnA	ColumnB
Dog	1
Cat	2
Dog	3
Cat	4
Blah	5
Cat	formula

<tbody>
</tbody>

Now in cell B6 (where I have written 'formula'), I want to insert the reverse lookup function (which I think I found somewhere on this site):

LOOKUP(2,1/($A$1:$A5=$A6),$B$1:$B5)

What this will do is lookup (from the bottom) the value A6 (i.e. "Cat") in the list A1:A5, and when it finds it, returns the corresponding value from column B (i.e. "4").

So far so good. Now what I want to do is dynamically change the size of the list in which the lookup takes place. So let's say that instead of wanting to search for "Cat" in A1:A5, I want to search it in A1:A3. After some googling, it seemed that INDIRECT would be the way to go, so in my original formula, I replaced "$A5" to get the following:

LOOKUP(2,1/($A$1:[COLOR=#ff0000]INDIRECT("F1")[/COLOR]=$A6),$B$1:$B5)

(where cell F1 contains the value "$A5")

I thought that since INDIRECT("F1") returns the value "$A5", this would have given me the same result as the original formula, but alas, no luck. I got a #N/A error instead. The Excel colour coding (which comes up when editing the forumula) shows me that only A1, A6 and B1:B5 are highlighted, meaning that obviously "INDIRECT("F1")" did not return the value "$A5" as required.

So the crux of this whole post is: How can I pass a function as a range value? Is it even possible? If not, then is there any other solution?

 

[ammarkr]

Ok cool, thanks. Do I have to mark this thread as resolved or something now?

 

[PaddyD]

Nope - feedback to people who've replied is all that's wanted...