Comination formula with IF and VLOOKUP, returning opposite results

[Jessicam3]

I have a complex formula going and for some reason the only way it returns the correct answer on the last portion of the formula is if the greater/less than sign is flipped around? I enlarged the specific sign that I have currently have flipped around so the formula works, but the way it should read is that if the vlookup is greater than, not less than. Can anyone help with this? Thanks!

=IF(F28=1,IF(VLOOKUP(G28,$AB$2:$AC$15,2,FALSE)>=T28,T28,T28+U28),IF(VLOOKUP(F28,$Z$2:$AA$26,2,FALSE)<=T28,T28,T28+U28))

 

[DanteAmor]

Hi @Jessicam3. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

=IF(F28=1,IF(VLOOKUP(G28,$AB$2:$AC$15,2,FALSE)>=T28,T28,T28+U28)

That part of the formula is true if F28 is equal to 1.
If it is not equal to 1 then the second part of your formula is done.

Your formula works fine, here are the 4 possible cases:

Dante Amor
	A				E	F	G	H	I	J									S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2																										1		1	2
3																										2	5	2	0
4																										3	1	3
5																										4		4
6																										5		5
7																										6		6
8																										7		7
9																										8		8
25																										24
26																										25
27
28						1	1		1											1	8
29						1	2		9											1	8
30						2			3											3	8
31						3			10											2	8
Hoja2

Cell Formulas
Range	Formula
I28:I31	I28	=IF(F28=1,IF(VLOOKUP(G28,$AB$2:$AC$15,2,0)>=T28,T28,T28+U28),IF(VLOOKUP(F28,$Z$2:$AA$26,2,0)>=T28,T28,T28+U28))

Review which of those cases does not work for you and tell us.
-------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data showing the problem.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Jessicam3]

Thank you I had no idea how to add this into the thread. Hopefully this section of my spreadsheet will help claify my issue. the highlighted rows are not calculating correctly.

FY24 wages.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	AB	AC
1																										Grade	max	Step	max
2																										1		ND	23.63
3																										2		A	24.30
4																										3	24.30	B	-
5																										4	26.33	C	-
6																										5	27.00	D	-
7																										6		E	25.65
8																										7	27.68	E-ND	27.00
9																										8		F	-
10																										9	30.38	G	27.00
11																										10		H	27.68
12																										11	32.40	I	28.35
13																										12		J	-
14																										13	31.73	K	29.36
15																										14
16																										15	37.13
17																										16
18																										17	43.88
19																							Months	years	raise	18
20																							0-11	less than a year	0.00	19	41.77
21																							12-35	1-2	0.25	20
22																							36-71	3-5	0.50	21	44.36
23																							72+	6+	0.75	22
24																										23	45.66
25																										24
26			months			grade								hours	Current	max	difference	test								25	-
27
28			11			19								80	26.95	41.77	(14.82)	(10.82)	4.00	30.95	0.00	30.95	64,376.00	0.00
29			36			19								80	27.20	41.77	(14.57)	(10.07)	4.00	31.20	0.50	31.70	65,936.00	0.00
30			204			3								80	21.86	24.30	(2.44)	0.31	2.00	23.86	0.75	24.61	51,188.80	0.00
31			18			5								80	16.50	27.00	(10.50)	(6.75)	3.50	20.00	0.25	20.25	42,120.00	0.00
32			31			9								80	18.75	30.38	(11.63)	(7.38)	4.00	22.75	0.25	23.00	47,840.00	0.00
33			357			5								50	25.72	27.00	(1.28)	2.97	3.50	29.22	0.75	29.22	37,986.00	0.00
34			216			5								60	19.55	27.00	(7.45)	(3.20)	3.50	23.05	0.75	23.80	37,128.00	0.00
35			34			13								80	21.75	31.73	(9.98)	(6.73)	3.00	24.75	0.25	25.00	52,000.00	0.00
36
Formula testing page ONLY

Cell Formulas
Range	Formula
P28:P35	P28	=IF(F28=1,VLOOKUP(G28,$AB$2:$AC$15,2,FALSE),VLOOKUP(F28,$Z$2:$AA$26,2,FALSE))
Q28:Q35	Q28	=O28-P28
R28:R35	R28	=Q28+(S28+U28)
T28:T35	T28	=IF(F28=1,IF(VLOOKUP(G28,$AB$2:$AC$14,2,FALSE)>=O28,O28,O28+S28),IF(VLOOKUP(F28,$Z$2:$AA$26,2,FALSE)<=O28,O28,O28+S28))
U28:U35	U28	=IF(C28>=72,$Y$23,IF(C28>=36,$Y$22,IF(C28>=12,$Y$21,IF(C28<=11,$Y$20))))
V28:V35	V28	=IF(F28=1,IF(VLOOKUP(G28,$AB$2:$AC$15,2,FALSE)>=T28,T28,T28+U28),IF(VLOOKUP(F28,$Z$2:$AA$26,2,FALSE)<=T28,T28,T28+U28))
W28:W35	W28	=V28*N28*26
X28:X35	X28	=IF(O28=V28,((U28+S28)*N28*26),0)

 

[ExceLoki]

seems like might be reading the formula like a sentence, not like a math equation.
the opening of the < or > will be open to the larger number for the statement to be true
• amount should be bigger < other amount =false
• amount should be bigger > other amount =true
dont think of it as larger amount is greater than other amount
more like: 1<2 & 2>1

so if you're trying to say that the vlookup is larger than t28, then
=IF(F28=1,IF(VLOOKUP(G28,$AC$2:$AD$15,2,0)>=T28,T28,T28+U28),IF(VLOOKUP(F28,$AA$2:$AB$26,2,0)>=T28,T28,T28+U28))
would do just that

 

[Fluff]

Can you explain why the two rows in blue are wrong, but the others are right.

 

[Jessicam3]

I see my own problem! I need to somehow take the formula one step farther or add another column. The value that is being added is making the total over the requred max, so I somehow have to account for that. Such as in row 33, the max is 27, and the starting value is 25.72, so the formula in Column T is allowing the addition of 3.50, but that makes the value higher than the set max. The same thing is happening in row 30 and column V. Any suggestions for that?

 

[Fluff]

What should they be?

 

[Jessicam3]

V30 should be 24.30, and T33 and V33 should be 27.00.

 

[Fluff]

Ok, how about

=IF(F28=1,IF(VLOOKUP(G28,$AB$2:$AC$15,2,FALSE)>=T28,T28,T28+U28),MIN(P28,IF(VLOOKUP(F28,$Z$2:$AA$26,2,FALSE)<=T28,T28,T28+U28)))