XLOOKUP & MIN

[kivikatz]

Hi All. I am having some issues in returning a value with the following formula. I am trying to return the cell reference ($C$1:$AZ$1) using XLOOKUP based on a 50% target increase ($A$1) after hitting the lowest point in a range (MIN(C2:AZ2)). The current formula I am using will return a blank cell if the 50% target in C2:AZ2 is reached before the lowest point is reached.

IF(XLOOKUP(MIN((C2:AZ2))*(1-$A$1),C2:BJ2,$C$1:$AZ$1,"",1)<XLOOKUP(MIN((C2:AZ2)),C2:AZ2,$C$1:$AZ$1,"",1),"",XLOOKUP(MIN((C2:BJ2))*(1-$A$1),C2:AZ2,$C$1:$AZ$1,"",1))

I'm sure there is a way of calculating this, but it is beyond my expertise (or lack thereof). I appreciate any help in solving this issue, including a better formula/function to find the relevant cell reference.

Below is a screenshot for better reference. Cell A2 calculation should ignore any 50% target increase reached before the MIN(C2:AZ2) is reached (I2) and only calculate 50% target increase reached after the MIN(C2:AZ2) is reached. The formula should return Q1 (25).

Hope you understand what I am trying to accomplish. Thanks.

.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Try this. I have assumed that the numbers in row 2 will always be negative since that is the case with your example.
If that is not the case, or this does not meet your need, can you posts a few more rows of sample data (with XL2BB) and include the expected results, manually entered & include any further clarification that you can?

kivikatz 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	50%		1	2	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
2	25		-0.22	-0.29	-0.58	-0.58	-0.59	-0.58	-0.6	-0.59	-0.42	-0.45	-0.42	-0.39	-0.36	-0.32	-0.28	-0.52	-0.5
Sheet1

Cell Formulas
Range	Formula
A2	A2	=INDEX(FILTER(C1:AZ1,(COLUMN(C1:AZ1)>MATCH(MIN(C2:AZ2),C2:AZ2,0)+COLUMN(C2)-1)*(C2:AZ2>=A1*MIN(C2:AZ2))),1)

 

[kivikatz]

Thank you Peter. That works really well. And thank you for the advice on XL2BB. I am attaching a screenshot with with my attempt at the formula using positive numbers based on a 50% target decrease ($A$1) after hitting the highest point in a range (the reverse of what I originally requested help on). For example, based on a high of 0.43 in Row 2, it returns 20 from Row 1. I think the formula works, but would appreciate any feedback.

I would also like to see if I can add a formula that returns the relevant cell reference in Row 1 based on a target (e.g. 0.40) being reached in row 2. I tried experimenting with Index/Match function, but had no success. In the example, for Row 2, it should return 10 from Row 1, since the target was reached before the preceding formula is triggered. Hope my screenshot explains what I am trying to do. Thanks again.

Xlookup Example.xlsx
	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	Z	AA
1	50%	Target	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25
2	20	0.40	-0.06	-0.04	-0.04	-0.04	-0.02	0.27	0.31	0.35	0.38	0.40	0.42	0.42	0.42	0.43	0.27	0.25	0.25	0.25	0.23	0.20	0.17	0.13	0.09	0.09	0.09
3	16	0.75	0.58	0.59	0.60	0.61	0.60	0.60	0.59	0.59	0.59	0.59	0.58	0.56	0.53	0.39	0.34	0.30	0.30	0.30	0.25	0.20	0.11	0.06	0.06	0.06	0.02
4
5		High
6		Trigger
Sheet2

Cell Formulas
Range	Formula
A2:A3	A2	=IFERROR(INDEX(FILTER($C$1:$AA$1,(COLUMN($C$1:$AA$1)>MATCH(MAX(C2:AA2),C2:AA2,0)+COLUMN(C2)-1)*(C2:AA2<=(1-$A$1)*MAX(C2:AA2))),1),"")

 

[Peter_SSs]

I am attaching a screenshot with with my attempt at the formula using positive numbers based on a 50% target decrease ($A$1) after hitting the highest point in a range

I'm confused by the description and sample data. For example ..

• You say using positive numbers but there is a mix of positive and negative numbers. Can you clarify?
• You have marked blue as "High", yet F3 is higher than G3 that you have marked blue. Can you clarify?
• Numbers in columns C:AA are showing 2 decimal places yet the actual numbers contain more decimal places. Should we be considering the shown value or the underlying value in those cells?
• Why have you marked S3 as the trigger in row 3 and not R3 (before) or T3 (after) when all 3 cells contain the same value (0.30 or underlying 0.2977)?

 

[kivikatz]

Thanks for the reply. Sorry for some of the errors. I was staring at the screen for so long that I was a bit sloppy in highlighting some of the cells. Guess I need glasses. Here are my clarifications for your questions.

• You say using positive numbers but there is a mix of positive and negative numbers. Can you clarify?
  • I want the formula to reflect a target decrease from the highest point in the range. The range can include both positive an negative numbers. I experimented with the formula you originally provided, and I think it works, but would like confirmation.
• You have marked blue as "High", yet F3 is higher than G3 that you have marked blue. Can you clarify?
  • Apologies for the sloppiness. It should have been F3.
• Numbers in columns C:AA are showing 2 decimal places yet the actual numbers contain more decimal places. Should we be considering the shown value or the underlying value in those cells?
  • I would like to consider the underlying value. I show only 2 decimals just to make the presentation more readable.
• Why have you marked S3 as the trigger in row 3 and not R3 (before) or T3 (after) when all 3 cells contain the same value (0.30 or underlying 0.2977)?
  • Apologies again for the sloppiness. It should have been R3, the first cell in the range that would trigger the formula (50% off of 0.61 high in F3).

Thanks again.

 

[Peter_SSs]

Thanks for the clarifications. Your A2:A3 formula looks fine to me.

For the new question, try this for row 2, & copied down. I'm not sure that I have interpreted what you want correctly though?

=INDEX(FILTER(C$1:AA$1,(C2:AA2>=B2)*(SEQUENCE(,COLUMNS(C2:AA2))<MATCH(A2,C$1:AA$1,0)),"N/A"),1)

 

[kivikatz]

Thanks a lot. Looks like its working out as intended.

 

[Peter_SSs]

Thanks a lot. Looks like its working out as intended.

Great. Thanks for the confirmation.