Multiple IF criteria

[johnawebbhsd]

I am trying to write a statement that will compare multiple criteria before returning a result, but having issues. Similair to Vlookup but looking at 2 cells first

Example I have 2 worksheets (sheet1, sheet2)

Sheet1 is for a City (Name in A1), Part#'s listed in A3-A?
Sheet2 has cities listed in A1-A800 each city has many rows, and parts in E part numbers may repeat, but only 1 per city, Qnty is in F...

Trying to write in C3 to look at A1 text (CITY) A3 (Part#), then compare to Sheet 2 and find where the match (CITY) ColumnB and (Part#) columnE, and return results from F if A1(sheet1) matches any B(sheet2) and if so, A3(sheet1) matches E(sheet2) of same row return F from that row....

Wow hope I said all that right.

 

[barry houdini]

Try this formula copied down the column

=SUMPRODUCT(--(Sheet2!$A$1:$A$800=$A$1),--(Sheet2!$E$1:$E$800=$A3),Sheet2!$F$1:$F$800)

or perhaps a more efficient solution which requires a concatenation column.....

If you concatenate columns A and E in sheet2 by using this formula in G1

=A1&"#"&E1

copied down column you can then use this formula in sheet1 C3, also copied down the column

=INDEX(Sheet2!$F$1:$F$800,MATCH($A$1&"#"&$A3,Sheet2!$G$1:$G$800,0))

 

[johnawebbhsd]

Wow.. that worked great... I would have never even tried to use SUMPRODUCT... Not exactly sure why to use SUMPRODUCT for this formula.. when you have time, or if, could you explain the formula...

Thanks again.. worked great...

 

[barry houdini]

Your problem is essentially a lookup, hence the alternative formula I suggested in my edited post above, but where your return values are numeric and there is only one match, as I believe is the case here, SUMPRODUCT will give the correct result.

The formula in effect sums all values in column F where column A matches your city and column E matches your part. In this case there should be only one match so the "sum" should just be of the one relevant figure.