Multiple If function

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
488
Office Version
  1. 2019
Platform
  1. Windows
Hello,
After sorting the data by ID No. with the help of a formula in column D, I am trying to get the name in column D as per books, IF THE ID no. is the same else it should display the same in Portal if the ID does not match. Like this formula '=IF(B2=B3, C=D3, E2).
QUERY NEW.xlsx
ABCDE
1DateAs PerID NO.FormulaNAME
201-02-2021Portal1234SUNDAYJANUARY
301-02-2021Books1234SUNDAYSUNDAY
401-02-2021Portal1234FEBRUARYJANUARY
501-02-2021Books2345FEBRUARYFEBRUARY
601-02-2021Portal2345MARCHMONDAY
701-02-2021Portal8888DECEMBERDECEMBER
801-02-2021Books3456MARCHMARCH
901-02-2021Portal3456MARCHTUESDAY
1001-02-2021Books4567MARCHMARCH
1101-02-2021Portal5678MAYWEDNESDAY
1201-02-2021Books5678MAYMAY
1301-02-2021Portal6789MONDAYFEBRUARY
1401-02-2021Books6789MONDAYMONDAY
1501-02-2021Portal7890APRILMARCH
1601-02-2021Books7890APRILAPRIL
1701-02-2021Portal1111MercuryMAY
1801-02-2021Books1111MercuryMercury
1901-02-2021Portal2222JupiterJUNE
2001-02-2021Books2222JupiterJupiter
2101-02-2021Portal3333PlutoJULY
2201-02-2021Books3333PlutoPluto
2301-02-2021Portal4444AUGUSTAUGUST
2401-02-2021Portal5555SEPTEMBERSEPTEMBER
2501-02-2021Portal6666OCTOBEROCTOBER
2601-02-2021Portal7777NOVEMBERNOVEMBER
Reconcile
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,921
Office Version
  1. 365
Platform
  1. Windows
It is not clear from your example what is original data and what should be returned by the formula.

If either column D or column E contain your expected results then there are a lot of errors that need correcting, if it is already correct then I don't see that there is any kind of logic to the results that could be used to create a formula.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
488
Office Version
  1. 2019
Platform
  1. Windows
It is not clear from your example what is original data and what should be returned by the formula.

If either column D or column E contain your expected results then there are a lot of errors that need correcting, if it is already correct then I don't see that there is any kind of logic to the results that could be used to create a formula.
The name as per portal (personal names)and Books (firm Names) are different. They are recognized by the ID no. to match. If I replace the names of the portal with the names as in the books it would be easier to use the pivot table. To do that the only common factor is the ID No. If there is a name in the portal and not in the books then it should display the name in the portal which again is matched with the ID No. In short, I want to replace the names as per books if the ID No. is same and names are different. And if the name is in the books and not in the portal then it should display the name as in the books.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
488
Office Version
  1. 2019
Platform
  1. Windows
The name as per portal (personal names)and Books (firm Names) are different. They are recognized by the ID no. to match. If I replace the names of the portal with the names as in the books it would be easier to use the pivot table. To do that the only common factor is the ID No. If there is a name in the portal and not in the books then it should display the name in the portal which again is matched with the ID No. In short, I want to replace the names as per books if the ID No. is same and names are different. And if the name is in the books and not in the portal then it should display the name as in the books.
One ID No. should not have 2 names. I have inserted an extra column that is D to get the names by formula
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,921
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Look at columns D and E for ID 1234, with the logic you describe the names do not match correctly.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
488
Office Version
  1. 2019
Platform
  1. Windows
Look at columns D and E for ID 1234, with the logic you describe the names do not match correctly.
As per Portal there are 2 entries of January and as per books there is only one entry. As the Id is the same all the 3 names should be Sunday. So when I use the pivot table I will know which entry is not in the books or not in the portal.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,921
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Your example also has February next to that ID in one row.

Are columns D and E both original data that needs to be checked, or is one of those columns a very in accurate attempt at showing the expected results?
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
488
Office Version
  1. 2019
Platform
  1. Windows
Your example also has February next to that ID in one row.

Are columns D and E both original data that needs to be checked, or is one of those columns a very in accurate attempt at showing the expected results?
Sorry, My Mistake. I will re-enter the table and send.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
488
Office Version
  1. 2019
Platform
  1. Windows
QUERY NEW.xlsx
BCDEF
1DateAs PerID NO.FormulaNAME
201-02-2021Portal1111MercuryMAY
301-02-2021Books1111MercuryMercury
401-02-2021Portal1234SUNDAYJANUARY
501-02-2021Books1234SUNDAYSUNDAY
601-02-2021Portal1234SUNDAYJANUARY
701-02-2021Portal1234SUNDAYJANUARY
801-02-2021Portal2222JupiterJUNE
901-02-2021Books2222JupiterJupiter
1001-02-2021Books2345MONDAYFEBRUARY
1101-02-2021Portal2345MONDAYMONDAY
1201-02-2021Portal3333PlutoJULY
1301-02-2021Books3333PlutoPluto
1401-02-2021Books3456MARCHMARCH
1501-02-2021Portal3456MARCHTUESDAY
1601-02-2021Portal4444AUGUSTAUGUST
1701-02-2021Books4567MARCHMARCH
1801-02-2021Portal5555SEPTEMBERSEPTEMBER
1901-02-2021Portal5678MAYWEDNESDAY
2001-02-2021Books5678MAYMAY
2101-02-2021Portal6666OCTOBEROCTOBER
2201-02-2021Portal6789MONDAYFEBRUARY
2301-02-2021Books6789MONDAYMONDAY
2401-02-2021Portal7777NOVEMBERNOVEMBER
2501-02-2021Portal7890APRILMARCH
2601-02-2021Books7890APRILAPRIL
2701-02-2021Portal8888MARCHDECEMBER
Reconcile

I added a few more rows and forgot to sort by ID No. Except column D everything is original data
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
488
Office Version
  1. 2019
Platform
  1. Windows
This is Fluff's formula for some different data but the present data has an additional column C to match. I tried to edit and solve it but was not able to.
=IF(COUNTIFS(F$2:F$5146,F2,D$2:D$5146,F2),F2,"Not Available"). In place of Not available I want it to take the name from the books. I can't edit as it is too complicated for me. Hope it helps you to solve
 

Watch MrExcel Video

Forum statistics

Threads
1,132,782
Messages
5,655,268
Members
418,183
Latest member
skaufman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top