Solved

combining to Excel IF statements into 1

Posted on 2016-08-19
22
63 Views
Last Modified: 2016-08-23
I have 2 Excel Workbooks.  The first Min Level Analysis is my main sheet and I would like to pull data from my Export.xls.  
I have this working for the following 2 formulas.

=IF('[Export.xls]SMExport 4.77'!A2="MVTS-PLG-BKK",'[Export.xls]SMExport 4.77'!C2,"0")
=IF(ISERROR(MATCH(A3,'[Export.xls]SMExport 4.77'!$B:$B, 0)), "0", '[Export.xls]SMExport 4.77'!C2)

Open in new window


I would like to combine these 2 IF statements into 1 similar to the following.  
=(IF('[Export.xls]SMExport 4.77'!A2="MVTS-BSE-BOS)) AND (IF(ISERROR(MATCH(A3,'[Export.xls]SMExport 4.77'!$B:$B, 0))), "0",'[Export.xls]SMExport 4.77'!C2))

Open in new window


The statement above is returning FALSE and not the value I need.

Can someone take a look and tell me where I am going wrong?

Thanks, Mike
Export.xls
Min-Levels-analysis-07-26-16.xlsx
0
Comment
Question by:morther
  • 7
  • 6
  • 4
  • +3
22 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41763107
Try

=(IF('[Export.xls]SMExport 4.77'!$A2="MVTS-BSE-BOS",IF(ISERROR(MATCH($A3,'[Export.xls]SMExport 4.77'!$B:$B,MATCH($A3,'[Export.xls]SMExport 4.77'!$B:$B,0))),"0",'[Export.xls]SMExport 4.77'!$C2)))

Open in new window


Is the value you're looking for "1" or "1588" ?
0
 
LVL 14

Expert Comment

by:Edward Pamias
ID: 41763108
When I opened the file there were paths to the C drive in the formula. Did you put those in there?
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41763115
The way I read your request was

IF '[Export.xls]SMExport 4.77'!$A2 = "MVTS-BSE-BOS" Then
	IF ISERROR (MATCH($A3,'[Export.xls]SMExport 4.77'!$B:$B) Then
		Return MATCH($A3,'[Export.xls]SMExport 4.77'!$B:$B,0)
	Else
		Return "0"
	End IF
Else '[Export.xls]SMExport 4.77'!$C2
End IF

Open in new window


Is this your intent?
1
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41763116
Edward, it is presuming both files are located in the same folder.  The C: drive references you see, I believe, are simply an internal function of Excel and have no bearing on where the original files were located.
1
 
LVL 14

Expert Comment

by:Edward Pamias
ID: 41763119
@ WalkaboutTigger Thanks for the info. :)
0
 

Author Comment

by:morther
ID: 41763346
Thanks WalkaboutTigger.  This looks like it is almost working.  When I get a match the number is appearing, but with no match it shows FALSE instead of 0.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41763351
I think this is what you are looking for

=IF('[Export.xls]SMExport 4.77'!A2="MVTS-BOS",'[Export.xls]SMExport 4.77'!C2,IF(ISERROR(MATCH(A3,'[Export.xls]SMExport 4.77'!$B:$B,0)),"0",'[Export.xls]SMExport 4.77'!C2))
0
 

Author Comment

by:morther
ID: 41763361
Well Saqib, this seems to not taking in to account the filter for MVTS-BSE-BOS  and collecting all records.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41763408
In the formula change
MVTS-BOS to MVTS-BSE-BOS

=IF('[Export.xls]SMExport 4.77'!A2="MVTS-BSE-BOS",'[Export.xls]SMExport 4.77'!C2,IF(ISERROR(MATCH(A3,'[Export.xls]SMExport 4.77'!$B:$B,0)),"0",'[Export.xls]SMExport 4.77'!C2))
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41765070
Alternative, using the AND(...) function:

=IF(AND('[Export.xls]SMExport 4.77'!A2="MVTS-BSE-BOS",ISERROR(MATCH(A3,'[Export.xls]SMExport 4.77'!$B:$B, 0))),0,'[Export.xls]SMExport 4.77'!C2)
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41765121
Edit walkabout's formula at the end to get that zero instead of false:
=(IF('[Export.xls]SMExport 4.77'!$A2="MVTS-BSE-BOS",IF(ISERROR(MATCH($A3,'[Export.xls]SMExport 4.77'!$B:$B,MATCH($A3,'[Export.xls]SMExport 4.77'!$B:$B,0))),"0",'[Export.xls]SMExport 4.77'!$C2),0))
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:morther
ID: 41765330
I think I was not being clear in my request; I am sorry.
This query is not pulling the correct cell data.  I am trying to pull column C in the "Export" workbook of the matching record.   The result for Part number 858-2161-00, from this query, is "3" but should actually be "0" because MVTS-BSE-BOS is not in this set.

MVTS-GLC      858-2162-00      2
MVTS-GLC-ENG      858-2161-00      6
MVTS-PLG-BKK      858-2161-00      1
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41765482
In which case, can you explain further how the columns in the two files are related?

Export File:
Your example above (858-2161-00) shows in 3 Warehouses:
MVTS-GLC
MVTS-GLC-ENG
MVTS-PLG-BKK

Analysis File:
858-2161-00 shows on row 6 but nothing to show Warehouse.
0
 

Author Comment

by:morther
ID: 41765606
Hello Rob:
My ultimate goal is to grab the value from the "Export" workbook (Column C) and place it in a separate column of "Min  Levels analysis" by Warehouse. MVTS-BSE-BOS,  MVTS-PLG-BKK,  MVTS-Taiwan, etc.  When we get one formula working, I will just copy it to the other columns and change the warehouse value.

I need to match "Min Levels analysis" workbook (Column A) "PN" to "Export" workbook "BIT PN" (Column B) based on "Export" workbook (Column A) "Warehouse".  Pull the value in "Export" (Column C) "Available" if there is a matching record else "0".
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41765631
But where is Warehouse in the file??
0
 

Author Comment

by:morther
ID: 41765681
Warehouse is in the "Export" workbook.  Because I will use a separate column in "Min Levels analysis" workbook, each column will have a formula similar to this.

=IF('[Export.xls]SMExport 4.77'!A2="MVTS-BSE-BOS",'[Export.xls]SMExport 4.77'(column C),"0")

Open in new window

or
=IF('[Export.xls]SMExport 4.77'!A2="MVTS-PLG-BKK",'[Export.xls]SMExport 4.77'(column C),"0")

Open in new window

or
=IF('[Export.xls]SMExport 4.77'!A2="MVTS-Taiwan",'[Export.xls]SMExport 4.77(column C),"0")

Open in new window


The other match will be PN = Bit PN
0
 

Author Comment

by:morther
ID: 41766169
I am still struggling with this.  To help clear this up I have uploaded a new Excel Sheet "MinLevelsAnalysis.xlsx".  There are 2 tabs, Analysis and Export.  Below is a SQL Statement that might better explain what I am trying to do in Excel.

SELECT Export.Available
FROM Analysis LEFT JOIN Export ON Analysis.PN = Export.BITPN
WHERE Analysis.PN = Export.BITPN AND Warehouse = "MVTS-PLG-BKK"

Open in new window

MinLevelsAnalysis.xlsx
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41766542
So how are you identifying the Warehouse in the Analysis sheet? Is it just going to be a column header and there will be a column for each Warehouse?

If so:

On Analaysis, Add "MVTS-BSE-BOS" to Q2 and then formula in Q3:

=SUMIFS(Export!$C:$C,Export!$A:$A,Q$2,Export!$B:$B,$A3)

This says:

Add up Export column C (Available) where Export Column A = Q2 ("MVTS-BSE-BOS") AND Export Column B = $A3 (PN).

If you add extra Warehouse References across R2, S2 etc and copy the formula across the references to Export sheet columns C and A will stay static but reference to column Q will move so referring to different Warehouse but reference to row 2 will stay the same so can be copied down.

Hope that is what you are looking for.

Thanks
Rob H
0
 

Author Closing Comment

by:morther
ID: 41767170
OMG Rob, you got it.  Thank you for not giving up on this.  This is exactly what I was looking for.  I am not sure if I completely understand what this formula is doing but I will look at it a bit more, but this is working and what I needed.  Thank you so much, again.  Mike
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41767242
Glad to be able to help and get there in the end.

To explain the formula, the syntax is:

=SUMIFS(SumRange,CriteriaRange1,Criteria1,CriteriaRange2,Criteria2)

CriteriaRange and Criteria can be more numerous if required, not sure of max number of criteria.

SUMRANGE - the range containing the data to be summed, in your example the Available Quantity, Export column C
CriteriaRange1 - The range containing the value to be checked against the criteria, Export Column A, Warehouse
Criteria1 - the value to be found in CriteriaRange1, referenced to Warehouse in Q2

CriteriaRange2 - The range containing the value to be checked against the criteria, Export Column B, Part Number
Criteria2 - the value to be found in CriteriaRange2, referenced to PN column in Analysis.

The multiple criteria act as an AND condition so where both Criteria are met in the Export the value from the matching row of Available is included in the SUM. If the criteria had been met in multiple places, values from all matching rows of Available column would be included.

Hope that clears it up.
Thanks
Rob
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41767267
You know, I love it when I learn something from a question I participated in.

Rob, you are a guru!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41767274
Thank you for the compliment.

Actually, according to my Profile for the points I have earned over the years I am a Genius; I went through Guru some time ago and have a t-shirt to show for it. ;-)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now