VLOOKUP to pull data from multiple columns

Hello new friends

I need to know how to use the vlookup fomula to pull data from multiple columns rather than just one so that when data is returned, data will populate multiple cells in a given row. Any ideas?

The third parameter of the VLOOKUP function determines which row in the lookup table you want to get the data from, e.g.:

=VLOOKUP($A2,Sheet2!$A$1:$G$100,3,0)

where the data is returned from the 3rd column (C) of the table A1:G100 on Sheet2.

However, you can make this variable, so that as the formula is copied across the column number adjusts automatically, like this:

=VLOOKUP($A2,Sheet2!$A$1:$G$100,COLUMNS($A:C),0)

Here the term COLUMNS($A:C) initially returns 3, but as the formula is copied across this term will become COLUMNS($A:D), then COLUMNS($A:E), thus returning 4 then 5, and so on.

Hope this helps.

Pete

Pete_UK

Im using the formula buts its still only populating one cell.

SampleWorkbook.xls

Here is a sample workbook. I want to make it so that when I select branch manager on the user interface, it will populate cells A10 to C10 on the same page. The cells would contain the three names following the branch manager on the control interface.

Put this in A10:

Formula:

=VLOOKUP($A$1,ControlInterface!$A:$D,COLUMNS($A:B),0)

then copy into B10 and C10.

Use the drop-down in A1 to see the numbers change automatically.

Hope this helps.

Pete