Solved

please help with Excel Match function

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

832 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