Help- What formula to use for If column A matches AND column B matches then return column C.

[dkabouris]

I need assistance in returning the value 12.029. I need to make sure that column A matches and Column B matches and return the value that is in column C. Not sure if this is IF and, VLOOKUP or MATCH. a vlookup would work for one value, but I need both values to match before returning the appropriate value in column C. Please help!!

A & W Electric, Inc.	TRIR 2012	12.029
A & W Electric, Inc.	TRIR 2013	9.871
A & W Electric, Inc.	TRIR 2014	7.916

<TBODY>
</TBODY>

I need to populate the chart for hundreds of values as such:

Company	TRIR 2012	TRIR 2013	TRIR 2014
A & W Electric, Inc.	X	X	X

<TBODY>
</TBODY>

the X's are where my formulas will go.

 

[dreid1011]

You could use a SUMIFS.

In summary table below TRIR 2012 and copy across to TRIR 2014:

=SUMIFS($C$1:$C$3,$A$1:$A$3,"="&$A6,$B$1:$B$3,"="&B5)

C1:C3 = amounts
A1:A3 = company name
A6 = company name in summary table to match
B1:B3 = TRIR year
B5 = TRIR year in summary table to match

 

[Fishboy]

I need assistance in returning the value 12.029. I need to make sure that column A matches and Column B matches and return the value that is in column C. Not sure if this is IF and, VLOOKUP or MATCH. a vlookup would work for one value, but I need both values to match before returning the appropriate value in column C. Please help!!

A & W Electric, Inc.	TRIR 2012	12.029
A & W Electric, Inc.	TRIR 2013	9.871
A & W Electric, Inc.	TRIR 2014	7.916

<tbody>
</tbody>

I need to populate the chart for hundreds of values as such:

Company	TRIR 2012	TRIR 2013	TRIR 2014
A & W Electric, Inc.	X	X	X

<tbody>
</tbody>

the X's are where my formulas will go.

Hi dkabouris, welcome to the boards.

Based on your example above, you could put the following formula where your first X is and drag fill across:

=IF($A$6="A & W Electric, Inc.",VLOOKUP(B$5,$B$1:$C$3,2,FALSE),"")

Orange bold $A$6 is wherever the company name to check for is in your bottom table.
Green bold B5 is the lookup value, wherever the TRIR number above your formula is in the bottom table.
Red bold $B$1:$C$3 is the full range of the lookup values from the top table.

You would need to manually update the company name if there were other companies further down the bottom table.

Excel 2010

	A	B	C	D
5	Company	TRIR 2012	TRIR 2013	TRIR 2014
6	A & W Electric, Inc.	12.03	9.871	7.92

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[dkabouris]

Hi dkabouris, welcome to the boards.

Based on your example above, you could put the following formula where your first X is and drag fill across:

=IF($A$6="A & W Electric, Inc.",VLOOKUP(B$5,$B$1:$C$3,2,FALSE),"")

Orange bold $A$6 is wherever the company name to check for is in your bottom table.
Green bold B5 is the lookup value, wherever the TRIR number above your formula is in the bottom table.
Red bold $B$1:$C$3 is the full range of the lookup values from the top table.

You would need to manually update the company name if there were other companies further down the bottom table.

Excel 2010

	A	B	C	D
5	Company	TRIR 2012	TRIR 2013	TRIR 2014
6	A & W Electric, Inc.	12.03	9.871	7.92

<TBODY>
</TBODY>

Sheet1​

Hi- I do have hundreds of company's names to manually enter. But I am still not recieving the correct number because TRIR 2012 TRIR 2013 and TRIR 2014 are listed hundreds of times, therefore it returns the first time that TRIR 2012 is shown, not also to the respective company.

I could not get the SUMIF to work correctly either.... stuck.

Company	For	US TRIR
A & W Electric, Inc.	TRIR 2012	12.029
A & W Electric, Inc.	TRIR 2013	9.871
A & W Electric, Inc.	TRIR 2014	7.916
AC Technical Services	TRIR 2012	0.000
AC Technical Services	TRIR 2013	0.000
AC Technical Services	TRIR 2014	0.000

<COLGROUP><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9216" width=259><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6087" width=171><TBODY>
</TBODY>

Below is the chart that I am trying to populate. Yellow indicates that the formula is returning the wrong value.
=IF($A$3="AC Technical Services",VLOOKUP(C$1,'TRIR All Years'!$B$3:$C$970,2,FALSE),"")

Company	TRIR 2012	TRIR 2013	TRIR 2014
A & W Electric, Inc.	12.029	9.871	7.916
AC Technical Services	0	9.871	7.916

<COLGROUP><COL style="WIDTH: 341pt; mso-width-source: userset; mso-width-alt: 16184" width=455><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2759" width=78><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

 

[dreid1011]

Have you tried my suggestion in post #2?

 

[dkabouris]

You could use a SUMIFS.

In summary table below TRIR 2012 and copy across to TRIR 2014:

=SUMIFS($C$1:$C$3,$A$1:$A$3,"="&$A6,$B$1:$B$3,"="&B5)

C1:C3 = amounts
A1:A3 = company name
A6 = company name in summary table to match
B1:B3 = TRIR year
B5 = TRIR year in summary table to match

Do you see what I am doing incorrect?
=SUMIFS($C$2:$C$970,$A$2:$A$970,"="&$A4,$B$2:$B$970,"="&$B$1)
This is the formula that is entered below in B4=green (* this is the 4th row) I have rows hidden. The red 0 should be 5.553
I have a feeling I did something wrong....

Company	TRIR 2012	TRIR 2013	TRIR 2014
Adelhardt Construction Corporation	0	0	0
Advanced Services of Longwood, Inc.	0	0	0
Advantage Parking Services	0	0	0

<COLGROUP><COL style="WIDTH: 341pt; mso-width-source: userset; mso-width-alt: 16184" width=455><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2759" width=78><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

Company	For	US TRIR
Adelhardt Construction Corporation	TRIR 2012	0.000
Adelhardt Construction Corporation	TRIR 2013	0.000
Adelhardt Construction Corporation	TRIR 2014	0.000
Advanced Services of Longwood, Inc.	TRIR 2012	0.000
Advanced Services of Longwood, Inc.	TRIR 2013	5.553
Advanced Services of Longwood, Inc.	TRIR 2014	0.000
Advantage Parking Services	TRIR 2012
Advantage Parking Services	TRIR 2013
Advantage Parking Services	TRIR 2014

<COLGROUP><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9216" width=259><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6087" width=171><TBODY>
</TBODY>

 

[dkabouris]

yes, but it returns the incorrect value I think it is because TRIR 2012 /2013 and 2014 are listed multiple times (Hundreds of times) I commented below.

 

[dreid1011]

Are the tables on separate sheets?

 

[dkabouris]

Are the tables on separate sheets?

Sorry I should have clarified that. yes these tables are on 2 different sheets.

 

[dreid1011]

Sorry I should have clarified that. yes these tables are on 2 different sheets.

Ok, with that being said, you need to add in the sheet names into the formula:

=SUMIFS(Sheet1!$C$1:$C$3,Sheet1!$A$1:$A$3,"="&$A6,Sheet1!$B$1:$B$3,"="&B5)

C1:C3 = amounts
A1:A3 = company name
A6 = company name in summary table to match
B1:B3 = TRIR year
B5 = TRIR year in summary table to match

Change the Sheet1 above to the proper sheet name. If it has spaces, add single quotes ' ' around the name.

Also, I apologize for the delay in response, I was on vacation.