Help! Label a record depending on country and amount

[LRULES]

Hey Guys,
I am a newbie to excel and I am trying to make my job easier rather than counting one by one I am wondering if there is a formula i can use to help me make things quicker.
I have attached the excel sheet, but basically, this is what I am looking for.
There are 2 Columns, Value, and Country.
Value are range in different prices, and country is which country our company purchased the goods from.

So basically I am wondering if there is any IF formulas or anything else i can do so that there is a 3rd column that basically labels the data from from first 2 coulmns in Category A,B,C OR D.
Category A:*Products with a value of CAD$0 to $20 (Purchased from countries other than the US or MX)
Category B:*Products with a value of $0 to $40 (Purchased from the US or MX)
Category C: Products with a value of CAD$40.01 to $150 (Purchased from the US or MX)
Category D:*Anything, not Category A, B, or C

I would greatly appreciate any help with this, it would make my life so much easier. I have attached a sample here.

[FDibbins]

LRULES, welcome to the forum. However, what may be urgent to you, is not necessarily the case for us.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[bsalv]

Please Login or Register  to view this content.

[LRULES]

Wow, my friend, you are amazing!!
Thank you so much!!! You saved me sooo much time, you have no idea! truly appreciate the help!
PS: Any advice for a newbie like me on where to start if want to learn excel, any courses, books, I started a new job and will be needing excel quite a bit.
Thanks!

[bsalv]

I can give you courses in dutch, not in english, sorry.

See #2, rename your title into "label a record depending on country and amount" or something similar to avoid problems with the moderators.

[LRULES]

Will do, Thank you!

[janmorris]

@LRULES,

heres a couple of places to start:

https://www.excel-university.com/learn-excel-free/

https://trumpexcel.com/learn-excel/

https://spreadsheeto.com/

[LRULES]

Hey, Just wondering what do i have to change to the formula if the value and Cntry coulms are H & I instead of A & B like in the sample you looked at for me? Because i copied pasted the formula to the actual worksheet and it came up with an Error "#NAME?"

Like in this attachment .

Thanks for your help!

[JohnTopley]

2 "errors":

no "Tabel3" so I copied the table from your original post ("Table1")

Data in VAL appeared to be text so converted to GENERAL (using text-to-columns)

[LRULES]

Thanks for your help guys, I really appreciate it, I'm totally new to excel. So when I try copying and pasting data from a different excel sheet into the Formula generated sheet you guys have been nice enough to create for me. I just received errors. is it because I am copying and pasting from different excel sheets?
For Example Attached Sheet 1 is what my work sheet looks like, identical. So as you can see I have left the Value & Cntry columns blank, as i am inputting that data from a different excel sheet via copy and paste.
Sheet 2 (Attached) is what my source sheet looks like where i am copy pasting the data from. Once i copy and paste i get the same "#NAME?" error, even thought the colums letters are the same in both sheets.
I am so confused. can someone please fix this for me?

So basically i want to be able to copy paste value and cntry columns from Sheer 2 to Sheet 1 without losing the ability/formula that calculates which category the row falls into, A,B,C OR D.

if that makes sense..........

Thanks alot in advance...

[bsalv]

1. that small table, I renamed it "TBL_Category", has to be somewhere in sheet1.xlsx, you can move it to another worksheet, no problem, otherwise error.
2. I modified the formula, so that the result is "-" if value or country is empty
3. While copying your data, don't overwrite the headers !!! only the data, this means copying J5:K41 of sheet 2 to K5 of sheet 1

Edit: wrong range in number 3

[LRULES]

Amazing! exactly what I was looking for! You all are amazing, wish I had even 3% of the excel knowledge you all have. lol