Vlookup column change. columns to rows

[b19upj]

Hi,

I am looking for a way to move the vlookup along column at a time when i drag the formulae down (not across).
So basically i have got a table of data and have transposed the headings.
I want to do a vlookup for the items running down the rows to the table with those headings making up different columns.

After looking up that row item now in a verticle column, i'd like to find the highest number from that item.

For the vlookup i used =VLOOKUP($AL$3&$AM$3,$A:$AD,5,0) then i had to change the 5 to a 6, then 7, then 8 and so on manually for each row.
My formulae to find the highest number for this vlookup item is =large(E:E,1) but i want to find the highest number for each item, so will need to manually change each row formulae to F:F, G:G, H:H. - There must be a way to make the columns change as the formulae is dragged down each row

Any help is apreciated

TIA

[Tony Valko]

Try it like this...

=VLOOKUP($AL$3&$AM$3,$A:$AD,ROWS(A$1:A5),0)

ROWS(A$1:A5) evaluates to 5

As you drag copy the formula that will increment like this:

ROWS(A$1:A5) = 5
ROWS(A$1:A6) = 6
ROWS(A$1:A7) = 7
ROWS(A$1:A8) = 8
ROWS(A$1:A9) = 9
etc
etc

[b19upj]

Thanks for your reply. Maybe my explanation was bad.

I would like to show the largest number in AP6 from column E,
AP7 to show the largest number in column F,
AP8 to show the largest number in column G...
But this goes on finding the largest in each column until column AB (which would be in AP29)

How can i write a formulae in AP6 that does this when i drag it down to AP29

Many thanks.

[Tony Valko]

Try this...

Entered in AP6 and copied down as needed:

=MAX(INDEX(E:AB,0,ROWS(AP$6:AP6)))

[b19upj]

Works a charm.

Thanks!

[Tony Valko]

You're welcome. Thanks for the feedback!