Solved

please help with Excel Match function

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

Source DataThis (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:

Data to MatchI 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
Comment
Question by:gregholl
  • 2
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
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

by:ProfessorJimJam
ID: 40422957
=IF(OR(AND(A1="Austria",B1<=0.5),AND(A1="Belgium",B1<=0.5),AND(A1="Canada",B1<=2.26),"Ok","Not OK")
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
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

by:gregholl
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

by:Glenn Ray
ID: 40422976
Glad I could help.  

-Glenn
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question