How to Add Numerical Value to Over the limit countries using If statement with V lookup

[Henry_Falcon77]

Good morning All. Thank you to ExceLoki for helping me with this formula yesterday. How would I add the numerical overage to the formula? For now, it will say over or under the limit. For all overages, I would like it to state the numerical amount of overage with the word over beside it. For example, Belgium would be over 26 cents, so I would like to state 26 cents over. Or if Bangladesh was at 9000 earned, I would like the formula to be able to state 1000 over . I don't need the under values. Thank you in advance for your assistance. The 2 columns on the left are my thresholds for each country. Column C and D is what I see on my end with a few 1,000 .

 

[ExceLoki]

thanks for the call out... lol
try this
--------------

Book1
	A	B	C	D	E	F	G	H	I
1	Country	Threshold					Country Given	Total Earned	Result Formula
2	Bangladesh	8000					Bangladesh	8000	under the limit
3	Belgium	9000					Belgium	9001.26	over 1.26
4	Bulgaria	9000					Belgium	2000	under the limit
5	Canada	10000					Belgium	6000	under the limit
6	China	5000					Belgium	8000	under the limit
7	Cyprus	2000					Bangladesh	9000	over 1000
8	Czech Republic	5000
9	Egypt	3000
10	Estonia	5000
11	France	5000
12	German	5000
13	Iceland	9000
14	Indonesia	2000
15	Israel	3000
16	Jamaica	7500
17	Korea	2000
18	Latvia	5000
19	Morocco	2000
20	Netherlands	2000
21	Norway	2000
22	Pakistan	5000
23	Phillipines	3000
24	Poland	2000
25	Portugual	5000
26	Replublic of Slovenica	5000
27	Romania	2000
28	Slovak Republic	5000
29	Spain	5000
30	Thailand	3000
31	Trinidad & Tobago	2000
32	Tunisia	4000
Sheet1

Cell Formulas
Range	Formula
I2:I7	I2	=IF(VLOOKUP(G2,A:B,2,FALSE)<H2,"over "&ROUND(H2-VLOOKUP(G2,A:B,2,FALSE),2),"under the limit")

 

[Henry_Falcon77]

thanks for the call out... lol
try this
--------------

Book1
	A	B	C	D	E	F	G	H	I
1	Country	Threshold					Country Given	Total Earned	Result Formula
2	Bangladesh	8000					Bangladesh	8000	under the limit
3	Belgium	9000					Belgium	9001.26	over 1.26
4	Bulgaria	9000					Belgium	2000	under the limit
5	Canada	10000					Belgium	6000	under the limit
6	China	5000					Belgium	8000	under the limit
7	Cyprus	2000					Bangladesh	9000	over 1000
8	Czech Republic	5000
9	Egypt	3000
10	Estonia	5000
11	France	5000
12	German	5000
13	Iceland	9000
14	Indonesia	2000
15	Israel	3000
16	Jamaica	7500
17	Korea	2000
18	Latvia	5000
19	Morocco	2000
20	Netherlands	2000
21	Norway	2000
22	Pakistan	5000
23	Phillipines	3000
24	Poland	2000
25	Portugual	5000
26	Replublic of Slovenica	5000
27	Romania	2000
28	Slovak Republic	5000
29	Spain	5000
30	Thailand	3000
31	Trinidad & Tobago	2000
32	Tunisia	4000
Sheet1

Cell Formulas
Range	Formula
I2:I7	I2	=IF(VLOOKUP(G2,A:B,2,FALSE)<H2,"over "&ROUND(H2-VLOOKUP(G2,A:B,2,FALSE),2),"under the limit")

thanks for the call out... lol
try this
--------------

Book1
	A	B	C	D	E	F	G	H	I
1	Country	Threshold					Country Given	Total Earned	Result Formula
2	Bangladesh	8000					Bangladesh	8000	under the limit
3	Belgium	9000					Belgium	9001.26	over 1.26
4	Bulgaria	9000					Belgium	2000	under the limit
5	Canada	10000					Belgium	6000	under the limit
6	China	5000					Belgium	8000	under the limit
7	Cyprus	2000					Bangladesh	9000	over 1000
8	Czech Republic	5000
9	Egypt	3000
10	Estonia	5000
11	France	5000
12	German	5000
13	Iceland	9000
14	Indonesia	2000
15	Israel	3000
16	Jamaica	7500
17	Korea	2000
18	Latvia	5000
19	Morocco	2000
20	Netherlands	2000
21	Norway	2000
22	Pakistan	5000
23	Phillipines	3000
24	Poland	2000
25	Portugual	5000
26	Replublic of Slovenica	5000
27	Romania	2000
28	Slovak Republic	5000
29	Spain	5000
30	Thailand	3000
31	Trinidad & Tobago	2000
32	Tunisia	4000
Sheet1

Cell Formulas
Range	Formula
I2:I7	I2	=IF(VLOOKUP(G2,A:B,2,FALSE)<H2,"over "&ROUND(H2-VLOOKUP(G2,A:B,2,FALSE),2),"under the limit")

How did you get your excel skills? Is there a specific book you recommend? Thanks again for your help the past few days. ONe last question.
Is there a way to put the over amount incolumn J. I know you gave me the answer to what I specifically asked. I was wondering if it's possible to have the word "over" in column I, as you showed me originally yesterday but have the formula place the overage dollar amount directly to the column on its right.

 

[ExceLoki]

How did you get your excel skills? Is there a specific book you recommend? Thanks again for your help the past few days. ONe last question.
Is there a way to put the over amount incolumn J. I know you gave me the answer to what I specifically asked. I was wondering if it's possible to have the word "over" in column I, as you showed me originally yesterday but have the formula place the overage dollar amount directly to the column on its right.

self taught over the years and lots of practice. one suggestion is to be active on this board. help others keeps you going and learning from others as well.

do you mean like this?

how do i combined if with v lookup.xlsx
	G	H	I	J
1	Country Given	Total Earned	Result Formula	Overage
2	Bangladesh	$ 8,000.00	under the limit
3	Belgium	$ 9,001.26	over	$1.26
4	Belgium	$ 2,000.00	under the limit
5	Belgium	$ 6,000.00	under the limit
6	Belgium	$ 8,000.00	under the limit
7	Bangladesh	$ 9,000.00	over
Sheet1

Cell Formulas
Range	Formula
I2,I4:I7	I2	=IF(VLOOKUP(G2,A:B,2,FALSE)<H2,"over ","under the limit")
J2:J7	J2	=IF(I2="over",TEXT(ROUND(H2-VLOOKUP(G2,A:B,2,FALSE),2),"$#,##0.00"),"")
I3	I3	=IF(VLOOKUP(G3,A:B,2,FALSE)<H3,"over","under the limit")

 

[Henry_Falcon77]

self taught over the years and lots of practice. one suggestion is to be active on this board. help others keeps you going and learning from others as well.

do you mean like this?

how do i combined if with v lookup.xlsx
	G	H	I	J
1	Country Given	Total Earned	Result Formula	Overage
2	Bangladesh	$ 8,000.00	under the limit
3	Belgium	$ 9,001.26	over	$1.26
4	Belgium	$ 2,000.00	under the limit
5	Belgium	$ 6,000.00	under the limit
6	Belgium	$ 8,000.00	under the limit
7	Bangladesh	$ 9,000.00	over
Sheet1

Cell Formulas
Range	Formula
I2,I4:I7	I2	=IF(VLOOKUP(G2,A:B,2,FALSE)<H2,"over ","under the limit")
J2:J7	J2	=IF(I2="over",TEXT(ROUND(H2-VLOOKUP(G2,A:B,2,FALSE),2),"$#,##0.00"),"")
I3	I3	=IF(VLOOKUP(G3,A:B,2,FALSE)<H3,"over","under the limit")

Yes, that is perfect. I took a few excel courses at my local University but they seemed to focus more on forecasting in excel. I am going to start looking over all the post to increase my skill level.

 

[Fluff]

For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Multiple if statement
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Henry_Falcon77]

For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Multiple if statement
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

My apologies. Thank you for the information. I will read up on the rules and make sure I post properly in the future