What is the Excel VLOOKUP formula for returning values from a number of columns

I have a range of 10 contiguous cells in a single column. I want to enter the same VLOOKUP formula in each of the cells but return the relevant value from a lookup table made up of an index column and 10 value columns.

So, for example, =VLOOKUP(B7,LookupTable,2,1) will return the value in the 2nd column of the lookup table. And =VLOOKUP(B7,LookupTable,3,1) will return the value in the 3rd column of the lookup table.

What common formula can I use without having to change the column number for each cell ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paste this code in a module and simply run it :)

This will insert the formulas in Cell A1 to A9

Sub Sample()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    With ws
        For i = 2 To 10
            .Range("A" & i - 1).Formula = "=VLOOKUP(B7,LookupTable," & i & ",1)"
    End With
End Sub

Open in new window

Saqib Husain, SyedEngineerCommented:
I have demonstrated three different ways to use one formula and copy it.

You might like one or give us more details of your data layout and we shall try to help.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zack BarresseCEOCommented:
Without looking at the attachments, you could increment it by using the ROW() function...


Then the row below it, once you copied it down, would be a reference of A3 (thus 3), then A4 (thus 4), etc.

Zack BarresseCEOCommented:
@Sid: No need for a loop when you can perform the action once, if you want some additional efficiency.  You could use (if you wanted to use VBA) something like this...

Sub Sample()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    ws.Range("A2:A11").Formula = "=VLOOKUP(B7,LookupTable,ROW(A2),1)"
End Sub

Open in new window

With relative referencing, the references will update themselves as needed.

TocogroupAuthor Commented:
Thanks for all your suggestions.

Saqib, especial thanks for your Excel Worksheet solutions.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.