• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 46
  • Last Modified:

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

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
endurance
Asked:
endurance
  • 7
  • 5
2 Solutions
 
wsh2Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this....

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

Open in new window

and copy it down.
0
 
enduranceAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
enduranceAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
enduranceAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
enduranceAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
enduranceAuthor Commented:
Perfect! Thank you!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now