IF TWO CELLS MATCH TWO CELLS IN SELECTION CELLS RETURN VALUE.

[MitchellDTExcel]

Hi, I have two tabs on my worksheet and I want to do the following:

SHEET1: Has a list of project numbers with their corresponding task numbers. Project numbers in the B column and task numbers in the C column.
SHEET2: Has a list of project numbers, their corresponding task numbers and task number values. Project numbers in the A column, task numbers in the B column and values in the E column.

I want excel to match the project number and task number in SHEET1, cell B6 and C6, to the same project number and task number in SHEET2, column A and column B and return the value in column E (on SHEET2).

 

[Eric W]

Welcome to the MrExcel forum!

With Sheet2 like this:

Book1
	A	B	C	D	E
5		Project	Task		Value
6		Project A	1		10
7		Project A	2		20
8		Project A	3		30
9		Project B	1		40
10		Project B	2		50
Sheet2

You can do the lookup like this:

Book1
	B	C	D	E	F
5	Project	Task		Value	Value
6	Project A	3		30	30
Sheet1

Cell Formulas
Range	Formula
E6	E6	=SUMIFS(Sheet2!E:E,Sheet2!B:B,B6,Sheet2!C:C,C6)
F6	F6	=IFERROR(INDEX(Sheet2!E:E,AGGREGATE(15,6,ROW(Sheet2!$B$6:$B$100)/(Sheet2!$B$6:$B$100=B6)/(Sheet2!$C$6:$C$100=C6),1)),"No match")

The E6 formula only works with numeric data. If you have text, use the F6 formula.