Match, VLookup possible formula needed
 

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?

Match, VLookup possible formula needed
 

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?

Match, VLookup possible formula needed
 

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?

Match, VLookup possible formula needed
 

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?

Match, VLookup possible formula needed
 

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?

Match, VLookup possible formula needed
 

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

Match, VLookup possible formula needed
 

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?

Match, VLookup possible formula needed
 

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?