Solved

Posted on 2014-11-04
101 Views
Here is what I need to do:

This (above) is the source data. I'm going to add the Match formula to the next blank column.

Out of the data, I need to check two fields: Column J (Country) and Column K (Weight)

So I am going to compare this data to the following table:

I have this (above) on Sheet2 of the Excel Workbook.

So, in the example:
(a) If a package is for Austria, AND it is 0.5 kg or less, then it is OK TO SHIP. Otherwise, it is NOT OK TO SHIP.
(b) If a package is for Belgium, AND it is 0.5 kg or less, then it is OK TO SHIP. Otherwise, it is NOT OK TO SHIP.
(c) If a package is for Canada, AND it is 2.26 kg or less, then it is OK TO SHIP. Otherwise, it is NOT OK TO SHIP.
(d) If a package is for Uruguay, then it is NOT OK TO SHIP (because Uruguay is not on this list).

Anyway, those are just four samples to explain what's going on here.

So long story short, I need to return the values "OK" or "NOT OK" based on the aforementioned criteria.

I know it's probably very simply for all you Excel Geeks out there :-D but my brain is frazzled at the moment. It's been a long day.

Thanks for the help!!!

Greg
0
Question by:gregholl
• 2
• 2

LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40422954
In cell L2, insert this formula and copy down:
=IF(K2<=IFERROR(VLOOKUP(J2,Sheet2!\$A\$2:\$B\$26,2,FALSE),0),"OK","NOT OK")

The IFERROR portion essentially returns a Max Weight of zero if the country is not found on the table in Sheet2, meaning any weight shown in column K for an invalid country would be "NOT OK".

Regards,
-Glenn
0

LVL 26

Expert Comment

ID: 40422957
0

LVL 26

Expert Comment

ID: 40422967
if you want to use it in the J and K columns here it is =IF(OR(AND(J2="Austria",K2<=0.5),AND(J2="Belgium",K2<=0.5),AND(J2="Canada",K2<=0.5),"Ok","Not OK")
0

LVL 1

Author Closing Comment

ID: 40422971
Thanks! Works great!

BTW ProfessorJimJam:
I need to deal with more countries than just Austria, Belgium and Canada. Check the original questions. There was a whole list of countries. Those were just examples.

Take care guys!
0

LVL 27

Expert Comment

ID: 40422976

-Glenn
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…