Dynamic range (column) in Lookup

[Cobber2008]

Hi all,

I have this formula LOOKUP(2,1/(($BB3:$CAT3>0)*($BB3:$CAT3>=$AW3)),COLUMN(3:3)) which finds the column number of its > the value in AW3. I am looking to make the reference column number CAT dynamic and equal to a number in cell A1 which is value 216 (COLUMN 216) being HH.

So based on that what i am trying to get to is this LOOKUP(2,1/(($BB3:$HH3>0)*($BB3:$HH3>=$AW3)),COLUMN(3:3))

How can i do this?? I Hope this makes sense? Thanks.

 

[Fluff]

How about

=LOOKUP(2,1/(($BB3:INDEX(3:3,,A1)>0)*($BB3:INDEX(3:3,,A1)>=$AW3)),COLUMN(3:3))

 

[Cobber2008]

Hi Fluff thanks for your reply - im not sure if it works exactly. if I look below assuming A1 = 7 and AW3 = 7 then
it should be (BB3:BH3) and the answer whould be 3 or column bd.

Does this make sense?

A1	7​
AW3	7​
bb	bc	bd	be	bf	bg	bh	bi	bj	bk
1​	2​	8​	7​	5​	6​	7​	8​	9​	10​

 

[Cobber2008]

it also should look from right to left for first value > aw3

 

[Fluff]

You originally said

equal to a number in cell A1 which is value 216

With that it will work, but not if you put 7 into A1

 

[Cobber2008]

You are right it does work thanks Fluff, much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.