Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria to return maximum value where x = x
Good morning,
I have an Excel file with two sheets, sheet 1 contains two columns of data, column A has text and column B has numbers. In sheet 2, I have a list if unique text that matches that in column A of sheet 1. I need to place a formula alongside the unique list in sheet 2 to return the highest corresponding value in sheet 1 i.e. the value in column B. Below is an example of what I'm looking to achieve. Any pointers most welcome. Thanks, Rob col a col b 01 30 02 29 01 31 02 30 01 34 col c col d 01 want to return 34 02 want to return 30 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria to return maximum value where x = x
Leading 0's are almost always a pain!
Try this array formula** : =MAX(IF(Sheet1!A$1:A$5=C1,Sheet1!B$1:B$5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. -- Biff Microsoft Excel MVP "Rob" wrote in message ... Good morning, I have an Excel file with two sheets, sheet 1 contains two columns of data, column A has text and column B has numbers. In sheet 2, I have a list if unique text that matches that in column A of sheet 1. I need to place a formula alongside the unique list in sheet 2 to return the highest corresponding value in sheet 1 i.e. the value in column B. Below is an example of what I'm looking to achieve. Any pointers most welcome. Thanks, Rob col a col b 01 30 02 29 01 31 02 30 01 34 col c col d 01 want to return 34 02 want to return 30 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria to return maximum value where x = x
Biff,
Just the job, worked as required. Saved me hours! Regards, Robert "T. Valko" wrote in message ... Leading 0's are almost always a pain! Try this array formula** : =MAX(IF(Sheet1!A$1:A$5=C1,Sheet1!B$1:B$5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. -- Biff Microsoft Excel MVP "Rob" wrote in message ... Good morning, I have an Excel file with two sheets, sheet 1 contains two columns of data, column A has text and column B has numbers. In sheet 2, I have a list if unique text that matches that in column A of sheet 1. I need to place a formula alongside the unique list in sheet 2 to return the highest corresponding value in sheet 1 i.e. the value in column B. Below is an example of what I'm looking to achieve. Any pointers most welcome. Thanks, Rob col a col b 01 30 02 29 01 31 02 30 01 34 col c col d 01 want to return 34 02 want to return 30 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria to return maximum value where x = x
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Rob" wrote in message ... Biff, Just the job, worked as required. Saved me hours! Regards, Robert "T. Valko" wrote in message ... Leading 0's are almost always a pain! Try this array formula** : =MAX(IF(Sheet1!A$1:A$5=C1,Sheet1!B$1:B$5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. -- Biff Microsoft Excel MVP "Rob" wrote in message ... Good morning, I have an Excel file with two sheets, sheet 1 contains two columns of data, column A has text and column B has numbers. In sheet 2, I have a list if unique text that matches that in column A of sheet 1. I need to place a formula alongside the unique list in sheet 2 to return the highest corresponding value in sheet 1 i.e. the value in column B. Below is an example of what I'm looking to achieve. Any pointers most welcome. Thanks, Rob col a col b 01 30 02 29 01 31 02 30 01 34 col c col d 01 want to return 34 02 want to return 30 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning a Min and Maximum Value by two criteria | Excel Discussion (Misc queries) | |||
Return Maximum from Column directly above Maximum in Row | Charts and Charting in Excel | |||
Maximum value based on criteria | Excel Discussion (Misc queries) | |||
Return Maximum value for Specific Month(s) | Excel Worksheet Functions | |||
Return Maximum value | Excel Discussion (Misc queries) |