Need a AHLookup function
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? |
Need a AHLookup function
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? |
All times are GMT +1. The time now is 06:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com