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

Hi,
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 ?

Thanks
Toco
###### 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.

Commented:
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
'=VLOOKUP(B7,LookupTable,2,1)
.Range("A" & i - 1).Formula = "=VLOOKUP(B7,LookupTable," & i & ",1)"
Next
End With
End Sub
``````

Sid
EngineerCommented:
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.

Saqib
LookupTable.xls

Experts Exchange Solution brought to you by

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

CEOCommented:
Without looking at the attachments, you could increment it by using the ROW() function...

=VLOOKUP(B7,LookupTable,ROW(A2),1)

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

HTH,
Zack
CEOCommented:
@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
``````

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

Zack
Author Commented: