Excel Check Vlookup value if cell value matches

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
204
Office Version
  1. 2019
Platform
  1. Windows
I have two Sheets "DATA" "RAW DATA"

In DATA sheets

Column A COLUMN B
APPLE 10
APPLE 15
APPLE 16.5
BANANA 35
BANANA 20
BANANA 36
ORANGE 45
ORANGE 15
ORANGE 50

In RAW DATA sheet in Column B I have to input APPLE/BANANA/ORANGE in various rows.

I want that if in B2 I have entered APPLE then in C2 I can enter only 10 or 15 or 16.5 if anything else I will enter then I will get error message that enter correct value. Similarly If I will enter Banana in column B of Raw Data sheet then I can only input values 35 or 20 or 36 in the column C

SO what will be the code for Column C of Raw Data sheet to do so ?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
749
Copy to sheet module of RAW DATA sheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dic, i As Long, LR As Long
Dim list As String
Dim Dws As Worksheet
    If Intersect(Target, Range("B:B")) Is Nothing Then
        Exit Sub
    Else
        Set Dws = Sheets("DATA")
        Set Dic = CreateObject("scripting.Dictionary")
    
        On Error Resume Next
        With Dws
            LR = .cells(Rows.Count, 1).End(xlUp).row
            For i = 1 To LR
                If .cells(i, 1).Value = Target.Value Then
                    Dic.Add .cells(i, 2).Value, .cells(i, 2).Value
                End If
            Next
        End With
    
        If Dic.Count = 0 Then
            MsgBox Target.Value & " does not exist in DATA sheet."
            Target.Offset(0, 1).Validation.Delete
            Exit Sub
        End If
        
        list = Join(Dic.keys, ",")
        
        With Target.Offset(0, 1).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=list
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
        Set Dic = Nothing
    End If
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,769
Messages
5,833,588
Members
430,221
Latest member
pgn29

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