Solved

please help with Excel Match function

Posted on 2014-11-04
5
97 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 25

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 25

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now