Multiple If function

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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.
 
Upvote 0
jasonb75 said:
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.
Click to expand...
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.
 
Upvote 0
RAJESH1960 said:
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.
Click to expand...
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:
Upvote 0
Look at columns D and E for ID 1234, with the logic you describe the names do not match correctly.
 
Upvote 0
jasonb75 said:
Look at columns D and E for ID 1234, with the logic you describe the names do not match correctly.
Click to expand...
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.
 
Upvote 0
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?
 
Upvote 0
jasonb75 said:
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?
Click to expand...
Sorry, My Mistake. I will re-enter the table and send.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Index match function
Replies
3
Views
439
Alex Blakenburg
A
H
  • Question
Cashflow forecast based on start dates and either monthly, bi-monthly, quarterly, annually
Replies
2
Views
429
felixstraube
F
A
  • Question
Insert new column(DATE) up to finish month and move to next sheet
Replies
0
Views
117
Amer Omar
A
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
232
SaraWitch
SaraWitch
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
228
Lukma
L

Forum statistics

Threads
1,214,649
Messages
6,120,731
Members
448,987
Latest member
marion_davis

Share this page

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
Back
Top