Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

combining to Excel IF statements into 1

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
Mike Orther
Asked:
Mike Orther
  • 7
  • 6
  • 4
  • +3
1 Solution
 
Darrell PorterEnterprise Business Process ArchitectCommented:
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
 
Edward PamiasTeam Lead RRS DeskCommented:
When I opened the file there were paths to the C drive in the formula. Did you put those in there?
0
 
Darrell PorterEnterprise Business Process ArchitectCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Darrell PorterEnterprise Business Process ArchitectCommented:
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
 
Edward PamiasTeam Lead RRS DeskCommented:
@ WalkaboutTigger Thanks for the info. :)
0
 
Mike OrtherSystems EngineerAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Mike OrtherSystems EngineerAuthor Commented:
Well Saqib, this seems to not taking in to account the filter for MVTS-BSE-BOS  and collecting all records.
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Rob HensonFinance AnalystCommented:
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
 
xtermieCommented:
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
 
Mike OrtherSystems EngineerAuthor Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Mike OrtherSystems EngineerAuthor Commented:
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
 
Rob HensonFinance AnalystCommented:
But where is Warehouse in the file??
0
 
Mike OrtherSystems EngineerAuthor Commented:
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
 
Mike OrtherSystems EngineerAuthor Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Mike OrtherSystems EngineerAuthor Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Darrell PorterEnterprise Business Process ArchitectCommented:
You know, I love it when I learn something from a question I participated in.

Rob, you are a guru!
0
 
Rob HensonFinance AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now