VLOOKUP: Help Converting Column to Row

[Cranners]

I'm trying to write function using VLOOKUP which takes the entries in a column of one sheet, and transfers them into the first row of the next sheet. The problem I'm having is when I try drag the function across the row, it matches with the next entry in the row of the first sheet, and if I drag down it recognizes the entry I want, but I need the heading across the row not down the column. Is there a way to do this without remaking my second sheet so that the table is vertical instead of horizontal?

Sorry if this is hard to understand, I'm pretty new to excel altogether.

Attached is an example of a table, what I'm trying to get after the VLOOKUP, and what I'm getting currently.

Thanks

[Olly]

In Sheet3!B1, enter:

Please Login or Register  to view this content.

and copy across.

Is that what you wanted?

[Roel Jongman]

Based on your sheet you can use the column() and row() functions to transpose the table to find ID 1 to 5 and the data columns as rows

Please Login or Register  to view this content.

whether you need to use -1 or +1 or other values depends on the starting column of the new table on sheet3. This table starts in B1 so B is column 2 and you and ID1 so need to do -1 to get ID 1.
for the 2ndd column of the sheet1 you need +1 because row of B1 = 1 and XXX is in the second column on sheet1

see attachment for result

[Olly]

If you're trying to transpose the entire table, then Power Query is a good bet. Format your data in Sheet1 as a table, then:

Please Login or Register  to view this content.