|
Using vlookup in a table that is not in ascending order
I have a table with the 1st column in ascending order but the column I wish
to find the maximum value in is not in ascending order. When I use vlookup it just returns the last number in the first column not the number that corresponds to the max number. If I could attach the spreadsheet to be more specific I would. Thanks in advance.. -- Duncan |
Using vlookup in a table that is not in ascending order
Do you want to return the value from the left column that corresponds to the
MAX value in the right column? =INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0)) -- Biff Microsoft Excel MVP "Parks" wrote in message ... I have a table with the 1st column in ascending order but the column I wish to find the maximum value in is not in ascending order. When I use vlookup it just returns the last number in the first column not the number that corresponds to the max number. If I could attach the spreadsheet to be more specific I would. Thanks in advance.. -- Duncan |
Using vlookup in a table that is not in ascending order
Yes
-- Duncan "T. Valko" wrote: Do you want to return the value from the left column that corresponds to the MAX value in the right column? =INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0)) -- Biff Microsoft Excel MVP "Parks" wrote in message ... I have a table with the 1st column in ascending order but the column I wish to find the maximum value in is not in ascending order. When I use vlookup it just returns the last number in the first column not the number that corresponds to the max number. If I could attach the spreadsheet to be more specific I would. Thanks in advance.. -- Duncan |
Using vlookup in a table that is not in ascending order
I tried the index function and I get the #N/A responce. Is there anyway I
can post the spreadsheet? -- Duncan "T. Valko" wrote: Do you want to return the value from the left column that corresponds to the MAX value in the right column? =INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0)) -- Biff Microsoft Excel MVP "Parks" wrote in message ... I have a table with the 1st column in ascending order but the column I wish to find the maximum value in is not in ascending order. When I use vlookup it just returns the last number in the first column not the number that corresponds to the max number. If I could attach the spreadsheet to be more specific I would. Thanks in advance.. -- Duncan |
Using vlookup in a table that is not in ascending order
Hi,
Check your "number" column, it might be formatted as text! Regards! Jean-Guy "Parks" wrote: I have a table with the 1st column in ascending order but the column I wish to find the maximum value in is not in ascending order. When I use vlookup it just returns the last number in the first column not the number that corresponds to the max number. If I could attach the spreadsheet to be more specific I would. Thanks in advance.. -- Duncan |
Using vlookup in a table that is not in ascending order
With Biff's formula, all that was needed was to absolute the first column
and copy left to right =INDEX($B$3:$B$93,MATCH(MAX(F3:F93),F3:F93,0)) =INDEX($B$3:$B$93,MATCH(MAX(G3:G93),G3:G93,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... I just tested Biff's and it worked just fine looking for the max value in col B and returning what is in col A. You may send your workbook to me at the address BELOW. -- Don Guillett Microsoft MVP Excel SalesAid Software "Parks" wrote in message ... I tried the index function and I get the #N/A responce. Is there anyway I can post the spreadsheet? -- Duncan "T. Valko" wrote: Do you want to return the value from the left column that corresponds to the MAX value in the right column? =INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0)) -- Biff Microsoft Excel MVP "Parks" wrote in message ... I have a table with the 1st column in ascending order but the column I wish to find the maximum value in is not in ascending order. When I use vlookup it just returns the last number in the first column not the number that corresponds to the max number. If I could attach the spreadsheet to be more specific I would. Thanks in advance.. -- Duncan |
| All times are GMT +1. The time now is 01:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com