Offset and Lookup to find value in column BEFORE last column used

[alyssa75]

I'm trying to convert this to give me the value in the cell 1 column to the left of this. I tried adding a simple =Offset(ref,0,1) with the ref being the formula below but it won't accept it.

=LOOKUP(9.99E+307,1/ISNUMBER(D5:BH5),D5:BH5)

Any help is greatly appreciated!

TIA!

 

[offthelip]

Use index and Match instead

 

[steve the fish]

You could make the result range C5:BG5

 

[Dryver14]

are you just returning the value 1 column over?

=INDEX(D5:BH5,MATCH(9.99E+307,D5:BH5)+1)

 

[alyssa75]

Thank you so much!! I used the index and match and just with the +1 to -1 to get the value in before instead of after.

Wondering if you might help me with one other thing. I want to pull the value from row 4 for whichever column is being referenced as a result of the above formula. Possible?

Thanks again!!

 

[alyssa75]

I figured out a workaround - so I am set.

I used =CELL("address",IF(E5=0,D5,INDEX(D5:BH5,MATCH(9.99E+307,D5:BH5)-1))) to get the cell address for the value I pulled
Then =MID(BL5,2,1)&4 to create a cell to store the cell address for what I wanted
Then =INDIRECT(BM5) to actually pull what was in that row.

 

[Dryver14]

you could do that or you could have just used the formula

=INDEX(D4:BH4,MATCH(9.99E+307,D5:BH5)-1)​

 

[Aladin Akyurek]

I'm trying to convert this to give me the value in the cell 1 column to the left of this. I tried adding a simple =Offset(ref,0,1) with the ref being the formula below but it won't accept it.

=LOOKUP(9.99E+307,1/ISNUMBER(D5:BH5),D5:BH5)

Any help is greatly appreciated!

TIA!

Suppose we have this in BD:BH5...

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="align: right"]80[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]

What is the value you want to return?