How can I fill cell values from two named ranges in excel?

0
votes

I have two named ranges

range 1 : test1, test2, test3, test4

range 2 : 20, 30, 50, 80

If I select one of the values from range 1, then the corresponding range 2 value should be copied to next cell. For example, if I select 'test 3' from dropdown, then text '50' should be copied to adjacent cell.

From the OP's comments below:
Sheet 2 has two named ranges - range 1 and range 2. In sheet 1, First column is assigned 'range 1'. If I select one of the values from range 1, then the adjacent cell should have corresponding 'range 2' value.

2
vbaexcelexcel-formula
Please included the VBA code snippet and add clarity to your task description: where are those Dropdown are located? Also, a fragment of the Worksheet with this sample data will be helpful. Best regards, – Alexander Bell
Sheet 2 has two named ranges - range 1 and range 2. In sheet 1, First column is assigned 'range 1'. If I select one of the values from range 1, then the adjacent cell should have corresponding 'range 2' value. I am not sure how to attach an excel sheet in stack overflow. I will try to do that. – Raja

2 Answers

2
votes

This should be all you need.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "B3" Then
        Application.EnableEvents = False
        Target.Offset(0, 1) = Range("range2") _
            (Application.Match(Target.Value, Range("range1"), 0))
        Application.EnableEvents = True
    End If
End Sub
0
votes

This formula finds the drop down text in range1 and recalls that location in range2.

Replace A1 with location of drop down selection.

Assumptions: Both arrays are of equal length.

=IFERROR( INDEX( range2, MATCH(D2, range1)), "•")