Find the address of the last value in a row, greater than zero, that is less than the value of a specific cell

[Trickle]

Hi All

I am looking for a formula that would give me the address of the cell furthest right in a row that contains a value less than that specified in a particular cell.

For example:

Could anyone please suggest a formula in N1 that would give me the address of the cell furthest right in the range A1:M1, which contains a value less than the value in P1, where the result is not blank or zero (in this example G1?)

Many thanks in advance for any help.

Best regards

Trickle

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	10	9.8	12	11.5	11	10.5	11	12	12.5	0				$G$1		11.5
Sheet4

Cell Formulas
Range	Formula
N1	N1	=ADDRESS(ROW(INDEX(A1:M1,1,MAX((--(A1:M1)<>"")*(--(A1:M1<>0))*(--(A1:M1<=P1))*COLUMN(A1:M1)))), COLUMN(INDEX(A1:M1,1,MAX((--(A1:M1)<>"")*(--(A1:M1<>0))*(--(A1:M1<=P1))*COLUMN(A1:M1)))))

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	10	9.8	12	11.5	11	10.5	11	12	12.5	0				$G$1		11.5
Sheet4

Cell Formulas
Range	Formula
N1	N1	=ADDRESS(ROW(INDEX(A1:M1,1,MAX((--(A1:M1)<>"")*(--(A1:M1<>0))*(--(A1:M1<=P1))*COLUMN(A1:M1)))), COLUMN(INDEX(A1:M1,1,MAX((--(A1:M1)<>"")*(--(A1:M1<>0))*(--(A1:M1<=P1))*COLUMN(A1:M1)))))

all the double hyphens may not be needed, it is just a habit of mine.

 

[awoohaw]

your post title mentions > 0 but the main post does not. The above doesn't exclude negative numbers. but can be changed with this (i have not verified it though):

=ADDRESS(
ROW(INDEX(A1:M1,1,MAX((--(A1:M1)<>"")*(--(A1:M1>0))*(--(A1:M1<=P1))*COLUMN(A1:M1)))),
COLUMN(INDEX(A1:M1,1,MAX((--(A1:M1)<>"")*(--(A1:M1>0))*(--(A1:M1<=P1))*COLUMN(A1:M1)))))

 

[PeteWright]

or this little shorter formula:

= ADDRESS(1,XMATCH(MAXIFS(A1:M1,A1:M1,"<"&P1),A1:M1,-1,-1))

EDIT:
feel free to "adjust" the criteria: "<"&P1 which means "less then the value of cell P1"
Simply put your operator ("<=" or ">=" etc.) between the quotes.

EDIT2:
Had a typo at the beginning of the formula.

 

[awoohaw]

or this little shorter formula:

= ADDRESS(A1,XMATCH(MAXIFS(A1:M1,A1:M1,"<"&P1),A1:M1,-1,-1),1,)

EDIT:
feel free to "adjust" the criteria: "<"&P1 which means "less then the value of cell P1"
Simply put your operator ("<=" or ">=" etc.) between the quotes.

not sure if user has XMATCH or MAXIFS functions.
Also, I think the first argument also should be Row(A1).

 

[Trickle]

Just out of bed in the UK. Many thanks Awoohaw and Pete for your help and for sharing your knowledge. I'll give this a go today and get back to you.

Best regards

Trickle

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also wondering why you want the address? Asking because many times similar questions have been asked it is to then use that cell address to do something else that can be done much more simply in another way without bothering with the address.

 

[Trickle]

Both long and short versions work perfectly. Many thanks, both for your help - it's much appreciated.

With best regards

Trickle