CODEWITHSUNDEEP
excel
Norman
Norman

Reputation: 6365

Excel VLookup Fill data in adjacent cells with data from other columns

How can I fill data in columns B, C & D from data in columns G, H, I based on the values in column A?

Here's my original data

1                           1   a   b   c
2                           2   d   e   f
3                           3   g   h   i
4                           4   j   k   l
5                           5   m   n   o
6                           6   p   q   r
7                           7   s   t   u
8                           8   v   w   x
9                           9   y   z   aa
10                          10  bb  cc  dd

The end result

    1   a   b   c       1   a   b   c
    4   j   k   l       2   d   e   f
    3   g   h   i       3   g   h   i
    5   m   n   o       4   j   k   l
    2   d   e   f       5   m   n   o
    7   s   t   u       6   p   q   r
    6   p   q   r       7   s   t   u
    8   v   w   x       8   v   w   x
    10  bb  cc  dd      9   y   z   aa
    9   y   z   aa      10  bb  cc  dd

The 1 in column matches the 1 in F and pulls data from G, H & I

I can do this using VLOOKUP and only a single column, but is it possible to get VLOOKUP to fetch and fill data in 3 columns?

Edit:

aa  a   b   c
bb  d   e   f
cc  g   h   i
dd  j   k   l
ee  m   n   o

Entering the formula below using ctrl+shift+enter gives me only aa

{=VLOOKUP("aa",$A$1:$D$5,{1,2,3,4},FALSE)}

Upvotes: 0

Views: 861

Answers (2)

VBA Pete
VBA Pete

Reputation: 2666

If you want to use VBA, you can create a simple FOR loop to find your values:

Sub CompleteColumns()

Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For x = 1 To lastrow
    For y = 2 To lastrow
        If Range("A" & x).Value = Range("F" & y).Value Then
            Range("B" & x & ":D" & x).Value = Range("G" & y & ":I" & y).Value
        Exit For
        End If
    Next y
Next x

End Sub

Upvotes: 1

ManishChristian
ManishChristian

Reputation: 3784

I am not sure how you are following the instructions, but you need to first select Cells B1, C1 and D1 and than without clicking anywhere else type this formula: 

=VLOOKUP($A1,F:I,{2,3,4},FALSE)

And then hit Ctrl+Shift+Enter.
Now keep Cells B1, C1 and D1 selected and darg it down till you have data in column A.

Here is the screenshot replicating your data:

enter image description here

Upvotes: 2

Related Questions