IF statement many scenarios

[odonovanc]

Let's say I have this data with the labeled columns:

A	B	C	D	E	F	G
7​	5​	7​	10​	14​	15.167​
120​	60​	84​	120​	168​	182​

Column A is an input. I need cell G1 to match the value in row 1 to what is in cell A2. So in this example, A2 is 120 so G1 would populate with 10. If A2 was 168 it would be 14. That part is straight forward, but there are rules. What goes in G1 has to be within 1 column of the representative value in A1. So A1 is 7, I can only have in G1 5, 7, or 10. If the value in A2 is less than 60, it would return 0. No matter how large the value is in A2, in this example, cell G1 cannot be larger than 10.

Please help.

 

[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’)

So A1 is 7, I can only have in G1 5, 7, or 10. .... No matter how large the value is in A2, in this example, cell G1 cannot be larger than 10.

Then why did you say this?

If A2 was 168 it would be 14.

.. and would the values in column F ever be used for anything?

Is the 7 in cell A1 relevant to the question in any way?

 

[odonovanc]

I made an error. If A2 was 168, G1 should be 10.

7 in cell A1 sets the parameters to possible values. Because A1 is 7 and matches column C, then only one column up or down value in A1 can be returned in G1.

A1 is an input. A2 is calculated based on a formula unrelated to A1. So A1 sets the 3 column window of options between columns B:F. Example, if A1 is 14, then the only options for G1 would be 10,14, or 15.167 since those are the values in D1:F1. A2 determines which of these 3 values are chosed. In this example, if A2 is below 120 (the value under 10) then a 0 would show, if 120-167 then a 10 would show, if 168 - 181 then a 14 would show, if 182 or above then 15.167. I hope this clarifies, I know this is complicated. I will look at updating my profile now.

 

[Peter_SSs]

Thanks for the clarification & updating your profile.
Not sure that I have it completely under control but see if this does what you want.
From your description, I take it that A1 could only be 7, 10 or 14 otherwise we couldn't go one column either way from that.

22 03 02.xlsm
	A	B	C	D	E	F	G
1	7	5	7	10	14	15.167	10
2	120	60	84	120	168	182
Lookup

Cell Formulas
Range	Formula
G1	G1	=LET(s,SEQUENCE(,3,MATCH(A1,B1:F1,0)-1),XLOOKUP(A2,INDEX(B2:F2,s),INDEX(B1:F1,s),0,-1))

 

[odonovanc]

This seems to be perfect. Thank you so much.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.