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]

Oh yeah, I tried different variations of the following too, thinking that perhaps quotes and ampersands were the way to go, but still no luck (these weren't even accepted by Excel):

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

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

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

 

[RoryA]

Actually you have too many quotes:
=LOOKUP(2,1/($A$1:INDIRECT(F1)=$A6),$B$1:$B5)

Note: no quotes round F1.

 

[ammarkr]

Hey Rory, thanks a lot for your help, I can't believe I wasted my whole day searching for a solution when it was so simple.

Can you help me out a bit further? After implementing the solution, I realised that INDIRECT isn't the best choice, since if I add or remove columns, it'll mess everything up. So how can I insert a formula as the "row number" if I already know the column?

For example, taking my same formula from above:

[COLOR=#333333]LOOKUP(2,1/($A$1:$A5=$A6),$B$1:$B5)[/COLOR]

Now instead of using INDIRECT to replace $A5, I just want to make the "5" a variable value, while keeping "$A" fixed. So let's say I want the value to be $A, followed by a MATCH function. This is what I tried:

[COLOR=#333333]LOOKUP(2,1/($A$1:"$A"&MATCH(C1,D:D)=$A6),$B$1:$B5)[/COLOR]

(The MATCH function is just an example, I could insert any formula which returns a value here)

Excel isn't accepting this as a formula though, and it keeps pointing me towards the quotes as the source of my error. Can you tell me where I'm messing up?

 

[ammarkr]

Ok, scratch that, having quotes around $A would defeat the purpose, since the only reason I'm inserting the column name directly instead of using INDIRECT is so that if I add or delete a column in the worksheet, it updates automatically. So I ideally I would want something like this:

[COLOR=#333333]LOOKUP(2,1/($A$1:$A&MATCH(C1,D:D)=$A6),$B$1:$B5)[/COLOR]

Meaning Excel recognises $A (the second instance of it) as a column instead of text, and updates it if a column is added or deleted.

 

[ammarkr]

Bump. Anyone?

 

[RoryA]

I suspect your best bet will be OFFSET or INDEX using a large range. But I also suspect that any such attempt to make the workbook foolproof will render it hugely inefficient.

 

[ammarkr]

Hahaha, great answer that It's just that I quite often find myself having to pass a function as an argument, and it gets really cumbersome to make a separate column to add the text for an INDIRECT first. I'll try INDEX though I guess

 

[ammarkr]

Umm, actually how exactly are we supposed to use OFFSET and INDEX to achieve what I want here?

 

[RoryA]

Something like:

LOOKUP(2,1/($A$1:INDEX(A:A,MATCH(C1,D:D))=$A6),$B$1:$B5)