hlookup and offset

[dmj120]

I'm trying to combine hlookup() and offset() to find the last value in a row, then go up 2 rows and left 1 cell. Each works independently, but when I try to combine them, errors occur.

=OFFSET(M3,-2,-1) --- "M3" is the below hlookup() reference
=HLOOKUP(9.99999999999999E+307,B3:Y3,1)

Is there a way to accomplish this?

 

[Sam_D_Ben]

Something like this.

=HLOOKUP(9.99999999999999E+307,B3:Y3,OFFSET(M3,-2,-1))

 

[dmj120]

Something like this.

=HLOOKUP(9.99999999999999E+307,B3:Y3,OFFSET(M3,-2,-1))

I tried that, it gives me a #REF ! error

 

[dmj120]

Trying something different (after watchin a MrExcel video about vlookup and offset), but now I get a value! error

=OFFSET(B4,0,HLOOKUP(9.99999999999999E+307,C4:Z4,1),-2,-1)

 

[Sam_D_Ben]

Something like this,

=OFFSET(INDEX(B3:I3,1,MATCH(A4,B3:I3,0)),-2,-1)

 

[dmj120]

Thanks, but nothing seems to work.

=OFFSET(HLOOKUP(9.99999999999999E+307,B3:Y3,1),-2,-1) this is essentially what I'm after, but .....is it scotch-thirty yet?

 

[AhoyNC]

Try:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Y
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14
2	13
3		14	25							25					25
Sheet

 

[dmj120]

Thanks, I'll try that

 

[Peter_SSs]

I would avoid the volatile function OFFSET altogether.
It appears you are working with numerical information, at least in the row where you are looking for the last value, so this should return the required value.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	C15	C16	C17	C18	C19	C20	C21	C22	C23	C24	C25
2
3	14	25							25					25
4
5	C13
Lookup

 

[dmj120]

I would avoid the volatile function OFFSET altogether.
It appears you are working with numerical information, at least in the row where you are looking for the last value, so this should return the required value.
Excel tables to the web >> Excel Jeanie HTML 4

Thanks Peter! That works perfectly! Thanks for the volatile link as well, I'm saving that