?
Solved

combining to Excel IF statements into 1

Posted on 2016-08-19
22
Medium Priority
?
83 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:Mike Orther
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 18

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 18

Expert Comment

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

Author Comment

by:Mike Orther
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:Mike Orther
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 33

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 18

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
 

Author Comment

by:Mike Orther
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 33

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:Mike Orther
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 33

Expert Comment

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

Author Comment

by:Mike Orther
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:Mike Orther
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 33

Accepted Solution

by:
Rob Henson earned 2000 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:Mike Orther
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 33

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 33

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

719 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