Is there a way to do a "lookup" and change the orientation of the

[PerryK]

I have a file that I use the VLookup function to find the data from other
files.

The worksheet is set up with Lables in column "A" and the "names of
managers" across Row 1 - starting in Cell A2.

Some of my data files are set up with the "Names of the Managers" listed in
column A and then the data in column B.

Is there an easy way to do a Lookup and transpose the information.

Or is there a way to do a Paste Link and Transpose at the same time?

Thanks,

 

[Nayab]

I have a file that I use the VLookup function to find the data from other
files.

The worksheet is set up with Lables in column "A" and the "names of
managers" across Row 1 - starting in Cell A2.

Some of my data files are set up with the "Names of the Managers" listed in
column A and then the data in column B.

Is there an easy way to do a Lookup and transpose the information.

Or is there a way to do a Paste Link and Transpose at the same time?

Thanks,

Instead of transposing the whole data, why not use a combination of
vlookup and hlookup?

 

[vezerid]

Probably you can do your job just using HLOOKUP instead of VLOOKUP.
HLOOKUP assumes an orientation that is typically transposed. Third
argument signifies row rather than column.

HTH
Kostis Vezerides

 

[PerryK]

The problem is that one of the data files is oriented to use VLookup and the
other is oriented to use HLookup.

I Need to figure out how to get the data from the table that is oriented for
VLookup into a table that could be used for HLookup.

Thanks

 

[vezerid]

Thinking...

Let us assume that you know a trait of the tables that allows you to
detect whether it has a vertical or horizontal orientation. E.g. you
know the label of the second column/row. Then you could have something
like

=IF(A2="label",VLOOKUP(...),HLOOKUP(...))

But the question is how you use these lookup functions. If you have
several such tables in several spreadsheets, how do you decide on
which sheet to look in the first place? Are you using INDIRECT? Also,
how do you specify the column (or row for HLOOKUP)?

You might have a central table where in the first column you have the
sheet name and table area and in the second column you have a V or H
to indicate the orientation. Say this range is called tables then you
might have something like:

=IF(VLOOKUP(F2,tables,2,0)="V",VLOOKUP(K2,INDIRECT(VLOOKUP(F2,tables,
1,0)),col,0),HLOOKUP(K2,INDIRECT(VLOOKUP(F2,tables,1,0)),row,0))

In either case who decides col and row?

HTH
Kostis

 

[PerryK]

Thanks for the response, but I think I may not have explained the problem
clearly.

The Final Report looks like this:
Bob Fred John Tim
Sales Dollars $50 $100 $250 $175
Sales Qty 2 5 7 9

I use the HLookup function to find the Sales Dollars in a Table that looks
like this:

Bob Fred John Tim
Sales Dollars $50 $100 $250 $175

This works with out any problems.

The problem is that the Sales Qty data is in a Table that is oriented like
this:

Sales Qty
Bob 2
Fred 5
John 7
Tim 9

What I am trying to do is get the Sales Qty into the "Report" aligned with
the correct Names.

Currently I have to copy the Sales Qty data and then Paste Special Transpose.
Then I use that table for the HLookup.

I have a large qty of data in these reports and it is time consuming to do
it this way.

That is why I am trying to see if there is an easier way to do it.

 

[vezerid]

Perry,

from what I see you did explain your situation clearly and my remarks
were towards this direction.

If a table has vertical orientation and you still want to use HLOOKUP
you can do it with an *array* formula (commit with Shift+Ctrl+Enter):

=HLOOKUP(A2,TRANSPOSE(table),2,0)

But why make your life harder? You can also use (no array-entering)

=VLOOKUP(A2,table,2,0)

Maybe you would like to send me part of your file; if I see the actual
conditions I might be able to suggest a functional solution. At any
rate, the suggestions of my previous post are still valid after your
last reply. My email is:

vezerid at act dot edu

Regards
Kostis