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

MitchellDTExcel

New Member
Joined
Nov 12, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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).
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
Welcome to the MrExcel forum!

With Sheet2 like this:

Book1
ABCDE
5ProjectTaskValue
6Project A110
7Project A220
8Project A330
9Project B140
10Project B250
Sheet2


You can do the lookup like this:

Book1
BCDEF
5ProjectTaskValueValue
6Project A33030
Sheet1
Cell Formulas
RangeFormula
E6E6=SUMIFS(Sheet2!E:E,Sheet2!B:B,B6,Sheet2!C:C,C6)
F6F6=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,208
Messages
5,640,856
Members
417,174
Latest member
diegomuser

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
Top