| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why must the table for Vlookup be sorted in ascending order? | New Users to Excel | |||
| Digits in ascending order ? | Excel Worksheet Functions | |||
| Pivot Table Ascending Order of Row Field | Excel Discussion (Misc queries) | |||
| Order Data in Pivot Table Non ascending or Decending | Excel Discussion (Misc queries) | |||
| Is there a VLOOKUP substitute when data is not in ascending order | Excel Worksheet Functions | |||
Linear Mode
