Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# combining to Excel IF statements into 1

Posted on 2016-08-19
Medium Priority
89 Views
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)
``````

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))
``````

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
Question by:Mike Orther
• 7
• 6
• 4
• +3

LVL 15

Expert Comment

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)))
``````

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

LVL 19

Expert Comment

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

ID: 41763115

``````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
``````

1

LVL 15

Expert Comment

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 19

Expert Comment

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

Author Comment

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

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

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

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 34

Expert Comment

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

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

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 34

Expert Comment

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

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 34

Expert Comment

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

Author Comment

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")
``````
or
``````=IF('[Export.xls]SMExport 4.77'!A2="MVTS-PLG-BKK",'[Export.xls]SMExport 4.77'(column C),"0")
``````
or
``````=IF('[Export.xls]SMExport 4.77'!A2="MVTS-Taiwan",'[Export.xls]SMExport 4.77(column C),"0")
``````

The other match will be PN = Bit PN
0

Author Comment

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"
``````
MinLevelsAnalysis.xlsx
0

LVL 34

Accepted Solution

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

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 34

Expert Comment

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

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

Rob, you are a guru!
0

LVL 34

Expert Comment

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

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micrâ€¦
###### Suggested Courses
Course of the Month11 days, 3 hours left to enroll