Vlookup

**tnoble17** · 01-23-2010, 10:12 AM

I have a worksheet that has a table called "Requirements" that list 60 different aircraft in column B vertically with the requirements listed horizontally (columns C thru AF) behind each aircraft number. (I am using column A for a key ID for the lookup use function in VLOOKUP.) I am building another worksheet to display the data per my choosing by row. I have tried to use VLOOKUP and the best I can get displayed is what in column C from the "Requirements" table. What do I need to do to display all the data in the row on the other worksheet?

**Richard Buttrey** · 01-23-2010, 10:19 AM

Hi,

Not quite sure what you're getting at here. A Vlookup can only return one value from a table. That's value is defined by the 3rd element in the Vlookup and refers to the column number of the table. If you're getting a column C value from a table that starts in column B then presumably you have specified the 2nd column.

However I suspect what you should be really using is the Data Filter functionality. This will either filter a table according to a criteria you select, or alternatively extract the records from a table to another sheet and/or range.

If you still have problems after trying the Data Filter, upload a workbook with a before and after position and no doubt someone will be able to advise further.

Regards

**teylyn** · 01-23-2010, 10:20 AM

You need to construct a Vlookup formula for each column in your target worksheet.

If you want to return the second column of the Vlookup table, use

=Vlookup(A1,Sheet1!$A$2:$AF:$2,2,False)

For the next column, use

=Vlookup(A1,Sheet1!$A$2:$AF:$2,3,False)

You can also construct a formula that can be copied across the columns, without having to adjust every single column if you use the COLUMN() function as the third argument of the Vlookup. Something like

=Vlookup(A1,Sheet1!$A$2:$AF:$2,COLUMN()+1,False)

COLUMN() returns the number of the current column, so A is 1, B is 2 , etc. You need to adjust the COLUMN() output by adding or subtracting the required number to arrive at the correct column for your Vlookup return..

hth