Using Excel 2003 SP2:

I have a table of which the reference row at the top of the table is
unsorted. For various reasons, sorting is not a practical solution. HLookup
does not function properly if the contents of the reference row are out of
order. If the table were inverted with the unsorted names in the left
column, I could use the AVLookup function; but Excel does not seem to have a
corresponding AHLookup function. Is there another function which would
accomplish the same thing as an AHLookup function?

Otherwise, the best workaround I can come up with is to create a second
table with formulae referencing the first, transpose it, and then reference
to this second table using the AVLookup function. This is not an ideal
solution because if the first table is expanded, then the second has to be
adjusted as well.

Any thoughts or suggestions?

You can use

suppose you have reference row from A1 to C1 and below is the data and
you need to lookup and find the value at A2 so the array entered
formula will be

=INDEX(A2:C2,MATCH(A1,A1:C1,0))

I hope this will work for you.

thanks

Shail

TRRed1 wrote:
Using Excel 2003 SP2:

I have a table of which the reference row at the top of the table is
unsorted. For various reasons, sorting is not a practical solution. HLookup
does not function properly if the contents of the reference row are out of
order. If the table were inverted with the unsorted names in the left
column, I could use the AVLookup function; but Excel does not seem to have a
corresponding AHLookup function. Is there another function which would
accomplish the same thing as an AHLookup function?

Otherwise, the best workaround I can come up with is to create a second
table with formulae referencing the first, transpose it, and then reference
to this second table using the AVLookup function. This is not an ideal
solution because if the first table is expanded, then the second has to be
adjusted as well.

Any thoughts or suggestions?