Creating multiple IFAND results in one help?!

[tga86]

Ok, I am self-teaching so bear with me if this is simple or even impossible or if i am confusing/overthinking things.
I have a table within a spreadsheet on Excel. An example of what I want to be able to do is;

In cell K23 i want the following to automatically calculate;
If the value in cell J22 is greater than cell b37 but less than cell b38 then I need to perform c38*j22-d38, or ifJ22 is greater than cell b38 but less than cell b39 I need to perform c39*j22-d39 or if j22 is greater than b39 but less than b40 I need to perform c40*j22-d40 and so on until row 45.

Is this possible? Have I made sense. I'm so sorry if not.
Thank you!

 

[tga86]

Ok, I am self-teaching so bear with me if this is simple or even impossible or if i am confusing/overthinking things.
I have a table within a spreadsheet on Excel. An example of what I want to be able to do is;

In cell K23 i want the following to automatically calculate;
If the value in cell J22 is greater than cell b37 but less than cell b38 then I need to perform c38*j22-d38, or ifJ22 is greater than cell b38 but less than cell b39 I need to perform c39*j22-d39 or if j22 is greater than b39 but less than b40 I need to perform c40*j22-d40 and so on until row 45.

Is this possible? Have I made sense. I'm so sorry if not.
Thank you!

I made it a little simpler (for me lol) by already calculating the final (perform) steps and putting them in column F.
So now I need to

If the value in cell J22 is greater than cell b37 but less than cell b38 then I need to enter value in F38, or ifJ22 is greater than cell b38 but less than cell b39 I need to enter value in F39 or if j22 is greater than b39 but less than b40 I need to enter value in F40 and so on until row 45

Does this make it easier? Thank you

 

[Joe4]

Welcome to the Board!

It seems to me like you should be able to create a lookup table and use VLOOKUP with approximate matching to return what you want.
Something like this shown here: VLOOKUP calculate grades

 

[Alex Blakenburg]

See if this works for you:
(you didn't say what you wanted to do in an = scenario)

20230414 Xlookup next larger tga86.xlsx
	B	C	D	E	F			I	J	K	L	M	N
22								Test Value >	35		Using Col F Helper	Without Helper
23										Result >	13994	13994
24
35
36	Col B	Col C	Col D		Col F
37	10	100	9		3491
38	20	200	8		6992
39	30	300	7		10493
40	40	400	6		13994
41	50	500	5		17495
42	60	600	4		20996
43	70	700	3		24497
44	80	800	2		27998
45	90	900	1		31499
46
Sheet1

Cell Formulas
Range	Formula
L23	L23	=XLOOKUP($J$22,$B$37:$B$45,$F$37:$F$45,"",1)
M23	M23	=XLOOKUP($J$22,$B$37:$B$45,$C$37:$C$45,"",1) * $J$22 - XLOOKUP($J$22,$B$37:$B$45,$D$37:$D$45,"",1)
F37:F45	F37	=C37*$J$22-D37

 

[tga86]

Welcome to the Board!

It seems to me like you should be able to create a lookup table and use VLOOKUP with approximate matching to return what you want.
Something like this shown here: VLOOKUP calculate grades

Thank you so much, I hadn't even discovered vlookup and it's functions yet and it is exactly what I needed . I needed to tweak and shift my results columns up 1 row but that works for me. Thank you so much!

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[Alex Blakenburg]

Thank you so much, I hadn't even discovered vlookup and it's functions yet and it is exactly what I needed . I needed to tweak and shift my results columns up 1 row but that works for me. Thank you so much!

The newer Xlookup is more flexible than vlookup. If you use the XLookup formula I provided you should not need to "shift your results columns up 1 row".
While VLookup will look up the previous item, the 1 parameter at the end of my XLookup tells it to look at the next larger item.

 

[tga86]

Thank you so much, I hadn't even discovered vlookup and it's functions yet and it is exactly what I needed . I needed to tweak and shift my results columns up 1 row but that works for me. Thank you so much!

Actually using the first formula in the L23 column seems to be working perfectly without needing to shift that final column! Thank you, have a great day/evening.

 

[tga86]

The newer Xlookup is more flexible than vlookup. If you use the XLookup formula I provided you should not need to "shift your results columns up 1 row".
While VLookup will look up the previous item, the 1 parameter at the end of my XLookup tells it to look at the next larger item.

Lol, sorry page hadn't reloaded with your reply. I've just seen your message and that explains perfectly why that works and the other didn't. Thanks

 

[Alex Blakenburg]

No problem. Glad it worked for you.