combining to Excel IF statements into 1

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

Select allOpen in new window

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

When I opened the file there were paths to the C drive in the formula. Did you put those in there?

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

Select allOpen in new window

Is this your intent?

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.

@ WalkaboutTigger Thanks for the info. :)

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.

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

Well Saqib, this seems to not taking in to account the filter for MVTS-BSE-BOS  and collecting all records.

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

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)

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

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

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.

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".

But where is Warehouse in the file??

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

Select allOpen in new window

or

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

Select allOpen in new window

or

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

Select allOpen in new window

The other match will be PN = Bit PN

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"

Select allOpen in new window

MinLevelsAnalysis.xlsx

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

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

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

Rob, you are a guru!

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. ;-)