Multiple IF criteria

johnawebbhsd

New Member
Joined
Apr 4, 2003
Messages
47
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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Apr 4, 2003
Messages
47
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,112
Messages
5,570,259
Members
412,314
Latest member
yazanwael
Top