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:
<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):
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:
(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?
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):
Code:
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:
Code:
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?