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

D

dkabouris

New Member
Joined
Oct 13, 2015
Messages
5
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!!



[TABLE="width: 384"]
<TBODY>[TR]
[TD="class: xl65, width: 259, bgcolor: transparent"]A & W Electric, Inc.
[/TD]
[TD="class: xl65, width: 82, bgcolor: transparent"]TRIR 2012
[/TD]
[TD="class: xl66, width: 171, bgcolor: transparent, align: right"]12.029
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A & W Electric, Inc.
[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2013
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]9.871
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A & W Electric, Inc.
[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2014
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7.916
[/TD]
[/TR]
</TBODY>[/TABLE]
I need to populate the chart for hundreds of values as such:
[TABLE="width: 495"]
<TBODY>[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]Company
[/TD]
[TD="class: xl66, width: 78, bgcolor: transparent"]TRIR 2012
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]TRIR 2013
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]TRIR 2014
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]A & W Electric, Inc.
[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]X
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]X
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]X
[/TD]
[/TR]
</TBODY>[/TABLE]


the X's are where my formulas will go.
 
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
dkabouris said:
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!!



[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 259, bgcolor: transparent"]A & W Electric, Inc.[/TD]
[TD="class: xl65, width: 82, bgcolor: transparent"]TRIR 2012[/TD]
[TD="class: xl66, width: 171, bgcolor: transparent, align: right"]12.029[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A & W Electric, Inc.[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]9.871[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A & W Electric, Inc.[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2014[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7.916[/TD]
[/TR]
</tbody>[/TABLE]
I need to populate the chart for hundreds of values as such:
[TABLE="width: 495"]
<tbody>[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]Company[/TD]
[TD="class: xl66, width: 78, bgcolor: transparent"]TRIR 2012[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]TRIR 2013[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]TRIR 2014[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]A & W Electric, Inc.[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]X[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]X[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]X[/TD]
[/TR]
</tbody>[/TABLE]


the X's are where my formulas will go.
Click to expand...
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
ABCD

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Company[/TD]
[TD="bgcolor: #FAFAFA, align: center"]TRIR 2012[/TD]
[TD="bgcolor: #FAFAFA, align: center"]TRIR 2013[/TD]
[TD="bgcolor: #FAFAFA, align: center"]TRIR 2014[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: center"]A & W Electric, Inc.[/TD]
[TD="bgcolor: #FAFAFA, align: center"]12.03[/TD]
[TD="bgcolor: #FAFAFA, align: center"]9.871[/TD]
[TD="bgcolor: #FAFAFA, align: center"]7.92[/TD]

</tbody>
Sheet1​
 
Upvote 0
Fishboy said:
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

<TBODY>
[TD="align: center"]5
[/TD]
[TD="bgcolor: #fafafa, align: center"]Company
[/TD]
[TD="bgcolor: #fafafa, align: center"]TRIR 2012
[/TD]
[TD="bgcolor: #fafafa, align: center"]TRIR 2013
[/TD]
[TD="bgcolor: #fafafa, align: center"]TRIR 2014
[/TD]

[TD="align: center"]6
[/TD]
[TD="bgcolor: #fafafa, align: center"]A & W Electric, Inc.
[/TD]
[TD="bgcolor: #fafafa, align: center"]12.03
[/TD]
[TD="bgcolor: #fafafa, align: center"]9.871
[/TD]
[TD="bgcolor: #fafafa, align: center"]7.92
[/TD]

</TBODY>
Sheet1​
Click to expand...

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.

[TABLE="width: 384"]
<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>[TR]
[TD="class: xl66, width: 259, bgcolor: transparent"]Company[/TD]
[TD="class: xl66, width: 82, bgcolor: transparent"]For[/TD]
[TD="class: xl67, width: 171, bgcolor: transparent"]US TRIR[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A & W Electric, Inc.[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2012[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]12.029[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A & W Electric, Inc.[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2013[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]9.871[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A & W Electric, Inc.[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2014[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]7.916[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]AC Technical Services[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2012[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]AC Technical Services[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2013[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]AC Technical Services[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2014[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.000[/TD]
[/TR]
</TBODY>[/TABLE]


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

[TABLE="width: 495"]
<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>[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]Company[/TD]
[TD="class: xl66, width: 78, bgcolor: transparent"]TRIR 2012[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]TRIR 2013[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]TRIR 2014[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]A & W Electric, Inc.[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]12.029[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]9.871[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]7.916[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]AC Technical Services[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]9.871[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]7.916[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
dreid1011 said:
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
Click to expand...
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....


[TABLE="width: 495"]
<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>[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]Company[/TD]
[TD="class: xl66, width: 78, bgcolor: transparent"]TRIR 2012[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]TRIR 2013[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]TRIR 2014[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]Adelhardt Construction Corporation[/TD]
[TD="class: xl67, bgcolor: transparent"]0[/TD]
[TD="class: xl67, bgcolor: transparent"]0[/TD]
[TD="class: xl67, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]Advanced Services of Longwood, Inc.[/TD]
[TD="class: xl67, bgcolor: transparent"]0[/TD]
[TD="class: xl67, bgcolor: transparent"]0[/TD]
[TD="class: xl67, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 455, bgcolor: transparent"]Advantage Parking Services[/TD]
[TD="class: xl67, bgcolor: transparent"]0[/TD]
[TD="class: xl67, bgcolor: transparent"]0[/TD]
[TD="class: xl67, bgcolor: transparent"]0[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 384"]
<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>[TR]
[TD="class: xl66, width: 259, bgcolor: transparent"]Company[/TD]
[TD="class: xl66, width: 82, bgcolor: transparent"]For[/TD]
[TD="class: xl67, width: 171, bgcolor: transparent"]US TRIR[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Adelhardt Construction Corporation[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2012[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Adelhardt Construction Corporation[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2013[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Adelhardt Construction Corporation[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2014[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Advanced Services of Longwood, Inc.[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2012[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Advanced Services of Longwood, Inc.[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2013[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]5.553[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Advanced Services of Longwood, Inc.[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2014[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Advantage Parking Services[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2012[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Advantage Parking Services[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2013[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Advantage Parking Services[/TD]
[TD="class: xl65, bgcolor: transparent"]TRIR 2014[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
dkabouris said:
Sorry I should have clarified that. yes these tables are on 2 different sheets.
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Return Last Value in Range Using INDEX MATCH
Replies
3
Views
605
Alex Blakenburg
A
C
  • Solved
Formula using match, indirect, and vlookup returning 0
Replies
1
Views
388
Ccruz444
C
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
576
STARL0RD
S
E
  • Question Question
Dynamic INDEX/MATCH or LOOKUP that can scan 2 or 3 columns and return dynamic list of results in correct cascading order
Replies
6
Views
260
Eduard_Stoo
E
M
  • Solved
return value based on the number that matches any number in three columns
Replies
3
Views
185
MMM_84
M

Share this page

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
Back
Top