If value in two columns match, return values from other columns

LostinVA

New Member
Joined
May 23, 2018
Messages
38
Hi everyone -

I'm trying to figure out how to compare values from Sheet1, Column A and Sheet 2, Column B, and if they match to return the values from Sheet2, Columns F, G, and I to Sheet1, Columns F, G, and H.

Confused yet? :) Hopefully that makes sense. Doesn't seem like it should be too complicated I just can't figure it out.

Thank you in advance!
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
It would help if you posted some sample data using XL2BB.
 

LostinVA

New Member
Joined
May 23, 2018
Messages
38
It would help if you posted some sample data using XL2BB.
Sorry, sample data below. Also, I mis-typed the comparison columns. This is what I'm looking to do: Compare values from Sheet1, Column A and Sheet 2, Column A, and if they match to return the values from Sheet2, Columns F, G, and I to Sheet1, Columns F, G, and H.

Sheet1:

Granite v C2F DIA 10Mb 12 Month.xlsx
ABCDEFGH
1AddressCarrierServiceMRCNRCC2F SupplierMRCNRC
2100 HUNTINGTON AVE, BOSTON, MA 02116Granite GridGrid FE - 10Mbps3601250
Granite


Sheet2:

Granite v C2F DIA 10Mb 12 Month.xlsx
ABCDEFGHI
1AddressCityStateZipcodeCountryMRCNRCTermSupplier
2333 Canal StNew OrleansLA70130USA56550012Cox Communications
C2F Low Rate


Thanks!
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
Well, this little data, I would have to assume you mean a row-by-row comparison that would look like this:

Book1
ABCDEFGH
1AddressCarrierServiceMRCNRCC2F SupplierMRCNRC
2100 HUNTINGTON AVE, BOSTON, MA 02116Granite GridGrid FE - 10Mbps3601250   
31 Main StreetWhateverGrid AA100200AT&T999888
Sheet1
Cell Formulas
RangeFormula
F2:F3F2=IF($A2=Sheet2!$A2,Sheet2!I2,"")
G2:H3G2=IF($A2=Sheet2!$A2,Sheet2!F2,"")


Book1
ABCDEFGHI
1AddressCityStateZipcodeCountryMRCNRCTermSupplier
2333 Canal StNew OrleansLA70130USA56550012Cox Communications
31 Main Street999888AT&T
Sheet2
 

LostinVA

New Member
Joined
May 23, 2018
Messages
38

ADVERTISEMENT

Well, this little data, I would have to assume you mean a row-by-row comparison that would look like this:

Book1
ABCDEFGH
1AddressCarrierServiceMRCNRCC2F SupplierMRCNRC
2100 HUNTINGTON AVE, BOSTON, MA 02116Granite GridGrid FE - 10Mbps3601250   
31 Main StreetWhateverGrid AA100200AT&T999888
Sheet1
Cell Formulas
RangeFormula
F2:F3F2=IF($A2=Sheet2!$A2,Sheet2!I2,"")
G2:H3G2=IF($A2=Sheet2!$A2,Sheet2!F2,"")


Book1
ABCDEFGHI
1AddressCityStateZipcodeCountryMRCNRCTermSupplier
2333 Canal StNew OrleansLA70130USA56550012Cox Communications
31 Main Street999888AT&T
Sheet2

Thanks for taking a look at it for me. There is a lot more data but I figured only the column headers and one row of data would be necessary to determine formulas. The formulas appear to just return what is in Column I & F from Sheet2.. it should only return a value when there's a match between Column A from Sheet1 and Column A from Sheet2. So if the addresses match, then populate Columns F, G, and H on Sheet1 with data from Columns F, G, and I from Sheet2.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
That's what the formulas did, I thought...when the addresses match, the data was copied. Otherwise, nothing was done.
If you provide a bit more data and what you expect when there's a match, that would help.

Maybe you don't mean row-by-row, but I'm only guessing with only 1 line of data per sheet.

Is this what you mean?

Book1
ABCDEFGH
1AddressCarrierServiceMRCNRCC2F SupplierMRCNRC
2100 HUNTINGTON AVE, BOSTON, MA 02116Granite GridGrid FE - 10Mbps3601250Sprint10102020
31 Main StreetWhateverGrid AA100200AT&T999888
Sheet1
Cell Formulas
RangeFormula
F2:F3F2=VLOOKUP($A2,Sheet2!$A$2:$I$100,9,FALSE)
G2:G3G2=VLOOKUP($A2,Sheet2!$A$2:$I$100,6,FALSE)
H2:H3H2=VLOOKUP($A2,Sheet2!$A$2:$I$100,7,FALSE)


Book1
ABCDEFGHI
1AddressCityStateZipcodeCountryMRCNRCTermSupplier
2333 Canal StNew OrleansLA70130USA56550012Cox Communications
31 Main Street999888AT&T
4100 HUNTINGTON AVE, BOSTON, MA 0211610102020Sprint
Sheet2
 
Last edited:

LostinVA

New Member
Joined
May 23, 2018
Messages
38

ADVERTISEMENT

That's what the formulas did, I thought...when the addresses match, the data was copied. Otherwise, nothing was done.
If you provide a bit more data and what you expect when there's a match, that would help.

Maybe you don't mean row-by-row, but I'm only guessing with only 1 line of data per sheet.

Is this what you mean?

Book1
ABCDEFGH
1AddressCarrierServiceMRCNRCC2F SupplierMRCNRC
2100 HUNTINGTON AVE, BOSTON, MA 02116Granite GridGrid FE - 10Mbps3601250Sprint10102020
31 Main StreetWhateverGrid AA100200AT&T999888
Sheet1
Cell Formulas
RangeFormula
F2:F3F2=VLOOKUP($A2,Sheet2!$A$2:$I$100,9,FALSE)
G2:G3G2=VLOOKUP($A2,Sheet2!$A$2:$I$100,6,FALSE)
H2:H3H2=VLOOKUP($A2,Sheet2!$A$2:$I$100,7,FALSE)


Book1
ABCDEFGHI
1AddressCityStateZipcodeCountryMRCNRCTermSupplier
2333 Canal StNew OrleansLA70130USA56550012Cox Communications
31 Main Street999888AT&T
4100 HUNTINGTON AVE, BOSTON, MA 0211610102020Sprint
Sheet2
Yes, I am a complete dope. Simple, straight forward vlookups. I am SO sorry for wasting your time. Thank you for your help, really appreciate it!
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
Never a waste of time...happy to have helped.
In the future, based on this experience, I hope you see that more info the better because otherwise anyone trying to find a solution for you is only taking pot-shots at it.
 

LostinVA

New Member
Joined
May 23, 2018
Messages
38
Never a waste of time...happy to have helped.
In the future, based on this experience, I hope you see that more info the better because otherwise anyone trying to find a solution for you is only taking pot-shots at it.
Absolutely agree! I will definitely provide more information next time. Love this forum, thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,427
Messages
5,601,605
Members
414,462
Latest member
StageRiis

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
Top