Compare values between two columns in different sheet

excelnoobhere

Board Regular
Joined
Mar 11, 2019
Messages
61
Hello all, excel noob here and i would appreciate any help from an excpert.

I have a workbook that contains two pages. I want to be able to compare sheet1 column C to sheet2 column B then if it matches, I want to take the value in sheet 2 column D then pasted it at sheet1 column U.
for example

sheet 1

A B C D-T
11-11 222
11-13 333
11-13 444




Sheet 2
A B C D
11-11 222 descr. 3
11-12 333 descr. 5
11-13 000 descr. 6



after code sheet 1 becomes




sheet 1

A B C D-T U
11-11 222 3
11-13 333 5
11-13 444

I don't want anything deleted from sheet 1
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,391
Office Version
  1. 2016
Platform
  1. Windows
Hi Excelnoobhere,

Here's Sheet2 data
Book1
ABCD
111-11222descr.3
211-12333descr.5
311-13000descr.6
Sheet2


Here's Sheet1 data and the INDEX & MATCH:

Book1
ABCDEFGHIJKLMNOPQRSTU
111-112223
211-133335
311-13444 
4 
Sheet1
Cell Formulas
RangeFormula
U1:U4U1=IFERROR(INDEX(Sheet2!D:D,MATCH(Sheet1!B1,Sheet2!B:B,0)),"")
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Sounds like VLookup

varios 27jul2020.xlsm
BCUV
12223
23335
3444 
4
Sheet1
Cell Formulas
RangeFormula
U1:U3U1=IFERROR(VLOOKUP(C1,Sheet2!B:D,3,0),"")
 

excelnoobhere

Board Regular
Joined
Mar 11, 2019
Messages
61
Hi Excelnoobhere,

Here's Sheet2 data
Book1
ABCD
111-11222descr.3
211-12333descr.5
311-13000descr.6
Sheet2


Here's Sheet1 data and the INDEX & MATCH:

Book1
ABCDEFGHIJKLMNOPQRSTU
111-112223
211-133335
311-13444 
4 
Sheet1
Cell Formulas
RangeFormula
U1:U4U1=IFERROR(INDEX(Sheet2!D:D,MATCH(Sheet1!B1,Sheet2!B:B,0)),"")


this worked great thank you so much!

is there a way I can put this into a MACRO where it goes through all pages on the workbook and copies the quantity into each column starting at U (each page would have its own column that will transfer the value from D to the main page)?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,355
Members
416,096
Latest member
forevans

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