have three columns - data changes, need to find matching data if two of the
match the criteria

Data and Rows changes Find matching FTE data
if no match enter a zero
Lab & Dept stay constant
Lab Dept FTE Lab Dept FTE Answers
14 10 3.04 16 10 Need 5.45
14 15 5.63 16 15 formula 0
14 40 3.39 16 30 11.3
14 100 0.19 16 40 0
14 260 6.61 16 50 5.61
16 10 5.45
16 30 11.3
16 50 5.61

Is this possible?

Assuming your main table is in columns A through C.
E2 is the lab lookup, F2 is the dept lookup.
Expand or retract table as necessary.

=SUMPRODUCT(--($A$1:$A$100=E2),--($B$1:$B$100=F2),($C$1:$C$100))

--
John C


"Roibn L Taylor" wrote:

have three columns - data changes, need to find matching data if two of the
match the criteria

Data and Rows changes Find matching FTE data
if no match enter a zero
Lab & Dept stay constant
Lab Dept FTE Lab Dept FTE Answers
14 10 3.04 16 10 Need 5.45
14 15 5.63 16 15 formula 0
14 40 3.39 16 30 11.3
14 100 0.19 16 40 0
14 260 6.61 16 50 5.61
16 10 5.45
16 30 11.3
16 50 5.61

Is this possible?

worked perfectly thank you, i should have thought of this before as i used
the formula before a while back
Thanks again
--
Thanks for the help in advance


"John C" wrote:

Assuming your main table is in columns A through C.
E2 is the lab lookup, F2 is the dept lookup.
Expand or retract table as necessary.

=SUMPRODUCT(--($A$1:$A$100=E2),--($B$1:$B$100=F2),($C$1:$C$100))

--
John C


"Roibn L Taylor" wrote:

have three columns - data changes, need to find matching data if two of the
match the criteria

Data and Rows changes Find matching FTE data
if no match enter a zero
Lab & Dept stay constant
Lab Dept FTE Lab Dept FTE Answers
14 10 3.04 16 10 Need 5.45
14 15 5.63 16 15 formula 0
14 40 3.39 16 30 11.3
14 100 0.19 16 40 0
14 260 6.61 16 50 5.61
16 10 5.45
16 30 11.3
16 50 5.61

Is this possible?

Not a problem, thanks for the feedback :)
--
John C


"Roibn Taylor" wrote:

worked perfectly thank you, i should have thought of this before as i used
the formula before a while back
Thanks again
--
Thanks for the help in advance


"John C" wrote:

Assuming your main table is in columns A through C.
E2 is the lab lookup, F2 is the dept lookup.
Expand or retract table as necessary.

=SUMPRODUCT(--($A$1:$A$100=E2),--($B$1:$B$100=F2),($C$1:$C$100))

--
John C


"Roibn L Taylor" wrote:

have three columns - data changes, need to find matching data if two of the
match the criteria

Data and Rows changes Find matching FTE data
if no match enter a zero
Lab & Dept stay constant
Lab Dept FTE Lab Dept FTE Answers
14 10 3.04 16 10 Need 5.45
14 15 5.63 16 15 formula 0
14 40 3.39 16 30 11.3
14 100 0.19 16 40 0
14 260 6.61 16 50 5.61
16 10 5.45
16 30 11.3
16 50 5.61

Is this possible?

How would you alter this formula if the data in columns A and B were text
rather than numbers? For example, if the data looked like this:

Lab Dept FTE
A Sales 3.04
A Payroll 5.63

I think I need a VLOOKUP but can't figure out how to make it work.

"John C" wrote:

Assuming your main table is in columns A through C.
E2 is the lab lookup, F2 is the dept lookup.
Expand or retract table as necessary.

=SUMPRODUCT(--($A$1:$A$100=E2),--($B$1:$B$100=F2),($C$1:$C$100))

--
John C


"Roibn L Taylor" wrote:

have three columns - data changes, need to find matching data if two of the
match the criteria

Data and Rows changes Find matching FTE data
if no match enter a zero
Lab & Dept stay constant
Lab Dept FTE Lab Dept FTE Answers
14 10 3.04 16 10 Need 5.45
14 15 5.63 16 15 formula 0
14 40 3.39 16 30 11.3
14 100 0.19 16 40 0
14 260 6.61 16 50 5.61
16 10 5.45
16 30 11.3
16 50 5.61

Is this possible?

Try it.

CParker wrote:

How would you alter this formula if the data in columns A and B were text
rather than numbers? For example, if the data looked like this:

Lab Dept FTE
A Sales 3.04
A Payroll 5.63

I think I need a VLOOKUP but can't figure out how to make it work.

"John C" wrote:

Assuming your main table is in columns A through C.
E2 is the lab lookup, F2 is the dept lookup.
Expand or retract table as necessary.

=SUMPRODUCT(--($A$1:$A$100=E2),--($B$1:$B$100=F2),($C$1:$C$100))

--
John C


"Roibn L Taylor" wrote:

have three columns - data changes, need to find matching data if two of the
match the criteria

Data and Rows changes Find matching FTE data
if no match enter a zero
Lab & Dept stay constant
Lab Dept FTE Lab Dept FTE Answers
14 10 3.04 16 10 Need 5.45
14 15 5.63 16 15 formula 0
14 40 3.39 16 30 11.3
14 100 0.19 16 40 0
14 260 6.61 16 50 5.61
16 10 5.45
16 30 11.3
16 50 5.61

Is this possible?


--

Dave Peterson

As a follow-up, assume Sheet1 was set up like this:

A B C D
Product Dept 1 Dept 2 Dept 3
Hammer
Broom

I wanted to have a formula in B2 that would go to another worksheet (Sheet2)
and retrieve the number of Hammers in Dept 1. Assume that Sheet2 was
organized as follows:

A B C
Dept 1 Hammer 5
Dept 1 Broom 2
Dept 2 Hammer 8

Here is the formula I found to work to retrieve a value of 5 in cell B2 of
Sheet1:

=IF(ISNA(INDEX('Sheet2!$C:$C,MATCH(1,($A2='Sheet2' !$B$1:$B$2500)*(B$1='Sheet2'!$A$1:$A$2500),0))),0, INDEX('Sheet2'!$C:$C,MATCH(1,($A2='Sheet2'!$B$1:$B $2500)*(B$1='Sheet2!$A$1:$A$2500),0)))

After typing in the formula, you need to press Ctrl+Shift+Enter for it to
work. Hope that helps anyone in my same situation.

"CParker" wrote:

How would you alter this formula if the data in columns A and B were text
rather than numbers? For example, if the data looked like this:

Lab Dept FTE
A Sales 3.04
A Payroll 5.63

I think I need a VLOOKUP but can't figure out how to make it work.

"John C" wrote:

Assuming your main table is in columns A through C.
E2 is the lab lookup, F2 is the dept lookup.
Expand or retract table as necessary.

=SUMPRODUCT(--($A$1:$A$100=E2),--($B$1:$B$100=F2),($C$1:$C$100))

--
John C


"Roibn L Taylor" wrote:

have three columns - data changes, need to find matching data if two of the
match the criteria

Data and Rows changes Find matching FTE data
if no match enter a zero
Lab & Dept stay constant
Lab Dept FTE Lab Dept FTE Answers
14 10 3.04 16 10 Need 5.45
14 15 5.63 16 15 formula 0
14 40 3.39 16 30 11.3
14 100 0.19 16 40 0
14 260 6.61 16 50 5.61
16 10 5.45
16 30 11.3
16 50 5.61

Is this possible?

Try it like this:

Entered in B2:

=SUMPRODUCT(--(Sheet2!$A$1:$A$3=B$1),--(Sheet2!$B$1:$B$3=$A2),Sheet2!$C$1:$C$3)

Copy across then down as needed.

--
Biff
Microsoft Excel MVP


"CParker" wrote in message
...
As a follow-up, assume Sheet1 was set up like this:

A B C D
Product Dept 1 Dept 2 Dept 3
Hammer
Broom

I wanted to have a formula in B2 that would go to another worksheet
(Sheet2)
and retrieve the number of Hammers in Dept 1. Assume that Sheet2 was
organized as follows:

A B C
Dept 1 Hammer 5
Dept 1 Broom 2
Dept 2 Hammer 8

Here is the formula I found to work to retrieve a value of 5 in cell B2 of
Sheet1:

=IF(ISNA(INDEX('Sheet2!$C:$C,MATCH(1,($A2='Sheet2' !$B$1:$B$2500)*(B$1='Sheet2'!$A$1:$A$2500),0))),0, INDEX('Sheet2'!$C:$C,MATCH(1,($A2='Sheet2'!$B$1:$B $2500)*(B$1='Sheet2!$A$1:$A$2500),0)))

After typing in the formula, you need to press Ctrl+Shift+Enter for it to
work. Hope that helps anyone in my same situation.

"CParker" wrote:

How would you alter this formula if the data in columns A and B were text
rather than numbers? For example, if the data looked like this:

Lab Dept FTE
A Sales 3.04
A Payroll 5.63

I think I need a VLOOKUP but can't figure out how to make it work.

"John C" wrote:

Assuming your main table is in columns A through C.
E2 is the lab lookup, F2 is the dept lookup.
Expand or retract table as necessary.

=SUMPRODUCT(--($A$1:$A$100=E2),--($B$1:$B$100=F2),($C$1:$C$100))

--
John C


"Roibn L Taylor" wrote:

have three columns - data changes, need to find matching data if two
of the
match the criteria

Data and Rows changes Find matching FTE data
if no match enter a zero
Lab & Dept stay constant
Lab Dept FTE Lab Dept FTE Answers
14 10 3.04 16 10 Need 5.45
14 15 5.63 16 15 formula 0
14 40 3.39 16 30 11.3
14 100 0.19 16 40 0
14 260 6.61 16 50 5.61
16 10 5.45
16 30 11.3
16 50 5.61

Is this possible?