Retrive Value to empty cellSolved/Closed

Question

Hello, 

Hope you can help me...

The user select a value from a list (in Column B), then Excel need to find this value from a column (Column D) on another sheet, and return the corresponding value from Column C, into the original cell.

So... Sheet2 (Cell C2), need to get value from looking at the value in Sheet2 (Cell B2), then find that exact value in Sheet 1 (ColumnD), and terun the value adjacent to Sheet 1 (Column D) which is Sheet 1 (Column A), and the v alue in Sheet 1 Column A needs to be copied and pasted to Cell C2 (Sheet2)

Configuration: Windows 7 / Internet Explorer 9.0

TrowaD · Answer

Hi Ornitor,

If it's possible to move (or copy) Sheet1 ColumnA to Sheet1 ColumnE you could use VLOOKUP:

Sheet2 Cell C2: =VLOOKUP(B2,Sheet1!D$2:E$10,2)

If ColumnE is already used, you can use another column as long as it's to the right of ColumnD.

Best regards,
Trowa

TrowaD · Answer

Since I had some spare time, here is a code you might like:

Sub RetrieveValue()
Dim lRow, lRow2, x As Integer
lRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row

For Each cell In Sheets("Sheet2").Range("B2:B" & lRow)
x = 1
    Do Until x = lRow2
    x = x + 1
    If cell.Value = Sheets("Sheet1").Range("D" & x) Then _
    cell.Offset(0, 1).Value = Sheets("Sheet1").Range("A" & x).Value
    Loop
Next cell
End Sub
Best regards,
Trowa

Ornitor · Answer

The VLookup works..NICE!

Retrive Value to empty cell

3 responses

Similar discussions

Subscribe To Our Newsletter!