Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I lookup the combination of different columns and return a result if not equal in Excel?

Posted on 2016-07-28
13
Medium Priority
?
43 Views
Last Modified: 2016-07-28
First, I want to compare columns A and C if they are equal then I want to compare columns B and D. If they are also equal I want nothing to be written in column E if they are different I want column B to be written in column E.
I tried different combination of If and vlookups but I kept getting a bunch or errors("value")
New-Microsoft-Excel-Worksheet.xlsx
0
Comment
Question by:endurance
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 14

Expert Comment

by:wsh2
ID: 41733436
Place this formula into cell E1

     =IF(A1=C1,IF(B1=D1,"",B1),B1))

NB: Change row number (1) to whatever row you are placing the formula in.
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41733438
Please try this....

In E2
=IF(AND(A2=C2,B2=D2),"",B2)

Open in new window

and copy it down.
0
 

Author Comment

by:endurance
ID: 41733501
The spreadsheet I shared is only a small sample of my whole data. And the required classes are not necessarily on the same row and there are some data that is on column A but not on column C (these will be skipped), so A2=C2 doesn't work. I need something like a Vlookup (Do we have A2 in Column C, if we do does B2 match column D that corresponds to the value we matched on column C)
For example,
class1  PP1 class2   PP2 Corrected to PP1
a             1        z         4       15
z             15      f          10      1
v             14      a         4        1
f              1        g         23      -
g             23      b         7        -
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41733525
Then you may try something like this....

In E2
=IF(AND(COUNTIF(C:C,A2),COUNTIF(D:D,B2)),"",B2)

Open in new window

0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41733528
If this doesn't help, please upload a sample workbook with the desired output in col. E filled manually as you did in your first sample workbook.
0
 

Author Comment

by:endurance
ID: 41733564
Sorry that also didn't work since it just copied the value from the same row onto column E instead of searching through the columns. I attached a bit modified version to clarify what I'm actually trying to do.
New-Microsoft-Excel-Worksheet.xlsx
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41733586
Would you please explain the logic behind your input in col. E for few cells e.g. E2 and E3 only to understand the logic behind the output?
0
 

Author Comment

by:endurance
ID: 41733613
So class1's  PP1 is the latest information we have and every class's PP should be adjusted to class1's PP. So we see on column C2 (value F) the corresponding PP (column D) is 11 but on column B, class F's PP is 6. Therefore on column E2 we write down 6 to show that we need to make a change in class2 - PP2.
Same thing for C3 (value A), it's corresponding PP is 2 (column D), however, according to PP1 A is matched with 1. So we write down on E3 "1" to show we need to make a change.

Note: The Classes are never the same on the same column but PP's can have the same value, such as A can be 1 and Z can be 1 but there are no A=1 and A=2.

Let me know if you need further clarification. Thanks for the help!
0
 
LVL 33

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41733623
Does this work for you?

In E2
=IFERROR(VLOOKUP(C2,$A$2:$B$12,2,0),"-")

Open in new window

and copy it down.
0
 

Author Comment

by:endurance
ID: 41733670
That helped a little. But it copied everything on column B on E. I only need to see the change on column E. Otherwise it's difficult to pick up where we need to make changes without going through the whole data set.

I attached another spreadsheet. Notice A4-B4 and C10-D10 (value C) we have the same corresponding value (3), so on column E we put a dash mark to notify that we don't need to make a change. And also A9 (value E12) has a value of 9 but when with the code written above it gave me a dash instead of value 9 on E4.
Sample-for-Duration-Study.xlsx
0
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41733685
Okay try this....

In E2
=IF(ISNA(MATCH(C2,$A$2:$A$12,0)),"-",IF(D2=VLOOKUP(C2,$A$2:$B$12,2,0),"-",VLOOKUP(C2,$A$2:$B$12,2,0)))

Open in new window

0
 

Author Closing Comment

by:endurance
ID: 41733701
Perfect! Thank you!
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41733706
You're welcome. Glad to help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 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