Compare one cells from sheet 2 and paste the value in front of the cell

**jeevan123** · 12-15-2012, 01:00 AM

Hello,
I m new to excel with VB coding
Please help me
I have the table as follows:-

Shee1 1
A B
1 apple 10
2 mango 100
3 banana 1000

now if i go to sheet 2 and type in

A1 "Banana" then automatically there would be "1000" in cell B1

sheet 2
A B
1 Banana 1000
2 apple 10
3

suggest me any formula in vba

thanks

**Trebor76** · 12-15-2012, 01:20 AM

Hi jeevan123,

Welcome to the forum!!

Try this formula in cell B1 of Sheet2 and then copied as required:

=VLOOKUP(A1,Sheet1!A:B,2,FALSE)

Regards,

Robert

**jeevan123** · 12-15-2012, 02:44 AM

thanks for your reply

Plz see the attatchment

**jeevan123** · 12-15-2012, 05:20 AM

Hello Mr. Robert

I have tried again and my problem is solved now.

thanks,

Ms. Jeevan

**jeevan123** · 12-15-2012, 05:21 AM

Hello Trebor76

I have tried again and my problem is solved now.

thanks,

Ms. Jeevan

**Trebor76** · 12-15-2012, 05:26 AM

Try this event macro on Sheet1:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    'Written by Trebor76
    'Visit my website www.excelguru.net.au

    If Target.Column = 1 Then
        If (IsError(Evaluate("VLOOKUP(""" & Target.Value & """,Sheet2!A:B,2,FALSE)"))) = False Then
            Application.EnableEvents = False
                Target.Offset(0, 1).Value = Evaluate("VLOOKUP(""" & Target.Value & """,Sheet2!A:B,2,FALSE)")
            Application.EnableEvents = True
        End If
    End If

End Sub

Robert