Compare between two sheets

[bumfart66]

Hi , i have two sheets

Sheet One, column AB,C

Use Description	Circuit Type	Name
USE01	Small	TEST01
USE02	Small	TEST02
USE03	Small	TEST03
USE04	Small	TEST04
USE05	Small	TEST05
USE06	Small	TEST06
USE07	Small	TEST07
USE08	Small	TEST08
USE09	Small	TEST09
USE10	Small	TEST10
USE11	Small	TEST11
USE12	Small	TEST12
USE13	Small	TEST13
USE14	Small	TEST14
USE15	Small	TEST15
USE16	Small	TEST16
USE17	Small	TEST34
USE18	Small	TEST35
USE19	Small	TEST36
USE20	Small	TEST37
USE21	Small	TEST38
USE22	Small	TEST39
USE23	Small	TEST40
USE24	Small	TEST41
USE25	Small	TEST42
USE26	Small	TEST43
USE27	Small	TEST44

Sheet Two - column AB,C

Status	Name	Length (m)
active	TEST01	11746.63
active	TEST02	11746.63
active	TEST03	23493.25
active	TEST04	6732.72
active	TEST05	40502.18
active	TEST06	162008.73
active	TEST07	20766.88
active	TEST08	3366.36
active	TEST09	13465.43
active	TEST10	9416.77
active	TEST11	20568.17
active	TEST12	108823.46
active	TEST13	93290.01
active	TEST14	203796.95
active	TEST15	9145.56
active	TEST16	9145.56
active	TEST17	4572.78
active	TEST18	9145.56
active	TEST19	9145.56
active	TEST20	9145.56
active	TEST21	36582.23
active	TEST22	25744.69
active	TEST23	12872.34
active	TEST24	12872.34
active	TEST25	154565.05
active	TEST26	3730.34
active	TEST27	7460.67
active	TEST28	7460.67
active	TEST29	7460.67
active	TEST30	7460.67
active	TEST31	7460.67
active	TEST32	7460.67
active	TEST33	85997.57

What i would like, If the name on sheet two appears on sheet one, the length from sheet two is added to column D it would look like this

Use Description	Circuit Type	Circuit Reference Number	Length
USE01	Small	TEST01	11746.63
USE02	Small	TEST02	11746.63
USE03	Small	TEST03	23493.25
USE04	Small	TEST04	6732.72
USE05	Small	TEST05	40502.18
USE06	Small	TEST06	162008.73
USE07	Small	TEST07	20766.88
USE08	Small	TEST08	3366.36
USE09	Small	TEST09	13465.43
USE10	Small	TEST10	9416.77
USE11	Small	TEST11	20568.17
USE12	Small	TEST12	108823.46
USE13	Small	TEST13	93290.01
USE14	Small	TEST14	203796.95
USE15	Small	TEST15	9145.56
USE16	Small	TEST16	9145.56
USE17	Small	TEST34	Not in sheet two
USE18	Small	TEST35	Not in sheet two
USE19	Small	TEST36	Not in sheet two
USE20	Small	TEST37	Not in sheet two
USE21	Small	TEST38	Not in sheet two
USE22	Small	TEST39	Not in sheet two
USE23	Small	TEST40	Not in sheet two
USE24	Small	TEST41	Not in sheet two
USE25	Small	TEST42	Not in sheet two
USE26	Small	TEST43	Not in sheet two
USE27	Small	TEST44	Not in sheet two

 

[Peter_SSs]

Does this do what you want?
(I didn't understand why/how the heading in column C in your sample results changed from "Name" to "Circuit Reference Number" so I ignored that.)

Test.xlsm
	A	B	C	D
1	Use Description	Circuit Type	Name	Length
2	USE01	Small	TEST01	11746.63
3	USE02	Small	TEST02	11746.63
4	USE03	Small	TEST03	23493.25
5	USE04	Small	TEST04	6732.72
6	USE05	Small	TEST05	40502.18
7	USE06	Small	TEST06	162008.73
8	USE07	Small	TEST07	20766.88
9	USE08	Small	TEST08	3366.36
10	USE09	Small	TEST09	13465.43
11	USE10	Small	TEST10	9416.77
12	USE11	Small	TEST11	20568.17
13	USE12	Small	TEST12	108823.46
14	USE13	Small	TEST13	93290.01
15	USE14	Small	TEST14	203796.95
16	USE15	Small	TEST15	9145.56
17	USE16	Small	TEST16	9145.56
18	USE17	Small	TEST34	Not in Sheet two
19	USE18	Small	TEST35	Not in Sheet two
20	USE19	Small	TEST36	Not in Sheet two
21	USE20	Small	TEST37	Not in Sheet two
22	USE21	Small	TEST38	Not in Sheet two
23	USE22	Small	TEST39	Not in Sheet two
24	USE23	Small	TEST40	Not in Sheet two
25	USE24	Small	TEST41	Not in Sheet two
26	USE25	Small	TEST42	Not in Sheet two
27	USE26	Small	TEST43	Not in Sheet two
28	USE27	Small	TEST44	Not in Sheet two
Sheet1

Cell Formulas
Range	Formula
D2:D28	D2	=IFNA(VLOOKUP(C2:C28,Sheet2!$B$2:$C$34,2,0),"Not in Sheet two")
Dynamic array formulas.

 

[bumfart66]

Hi , thanks , that works really well

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[MikeVol]

Good day @bumfart66.
Another option for you using macros. Paste the following code into the standard module.

Option Explicit

Sub AddLengthToSheet1()
    Dim i           As Long

    Dim dict        As Object
    Set dict = CreateObject("Scripting.Dictionary")
    Application.ScreenUpdating = False

    With ThisWorkbook.Worksheets("Sheet2")

        For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

            If Not dict.exists(.Cells(i, "B").Value) Then
                dict.Add .Cells(i, "B").Value, .Cells(i, "C").Value
            End If

        Next i

    End With


    With ThisWorkbook.Worksheets("Sheet1")
        .Range(.Cells(2, "D"), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, "D")).ClearContents

        For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

            If dict.exists(.Cells(i, "C").Value) Then
                .Cells(i, "D").Value = dict(.Cells(i, "C").Value)
            Else
                .Cells(i, "D").Value = "Not in sheet two"
            End If

        Next i

        .Range("D1").Value = "Length (m)"
        .Range(.Cells(2, "D"), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, "D")).NumberFormat = "0.00"
        .Cells.Columns.AutoFit
    End With

    Set dict = Nothing
    Application.ScreenUpdating = True
End Sub

I hope I understood you correctly. I was glad to help you. Good luck.