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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,807
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,129,565
Messages
5,637,082
Members
416,956
Latest member
mitzhaki

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