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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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))
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,742
Messages
6,103,654
Members
447,876
Latest member
rodqntr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top