Match, VLookup possible formula needed

[Roibn L Taylor]

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?

 

[John C]

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))

 

[Roibn Taylor]

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

 

[John C]

Not a problem, thanks for the feedback

 

[CParker]

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.

 

[Dave Peterson]

Try it.

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.

 

[CParker]

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.

 

[T. Valko]

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.