Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value
I have this formula:
=OFFSET(INDIRECT(ADDRESS(MATCH(MAX(B1:B26),B1:B26, 0),2)),0,-1) It looks up the value one cell left of the largest value in B1:B26, and it works OK, but 1) Can it be done in an easier way? 2) It only works if the column starts in row 1. as Match find the row within the array and Address demands the row in the sheet. Jan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value
Try =INDEX(A1:A26,MATCH(MAX(B1:B26),B1:B26,0))
best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "jkrons" wrote in message ... I have this formula: =OFFSET(INDIRECT(ADDRESS(MATCH(MAX(B1:B26),B1:B26, 0),2)),0,-1) It looks up the value one cell left of the largest value in B1:B26, and it works OK, but 1) Can it be done in an easier way? 2) It only works if the column starts in row 1. as Match find the row within the array and Address demands the row in the sheet. Jan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value
Hi,
here's another way with an ARRAY formula =INDEX(A2:A20,MATCH(MAX(B2:B20),B2:B20,0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jkrons" wrote: I have this formula: =OFFSET(INDIRECT(ADDRESS(MATCH(MAX(B1:B26),B1:B26, 0),2)),0,-1) It looks up the value one cell left of the largest value in B1:B26, and it works OK, but 1) Can it be done in an easier way? 2) It only works if the column starts in row 1. as Match find the row within the array and Address demands the row in the sheet. Jan . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value
I hate to be pedantic, Mike, but the formulas we each suggested do NOT need
to be array entered. Bernard "Mike H" wrote in message ... Hi, here's another way with an ARRAY formula =INDEX(A2:A20,MATCH(MAX(B2:B20),B2:B20,0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jkrons" wrote: I have this formula: =OFFSET(INDIRECT(ADDRESS(MATCH(MAX(B1:B26),B1:B26, 0),2)),0,-1) It looks up the value one cell left of the largest value in B1:B26, and it works OK, but 1) Can it be done in an easier way? 2) It only works if the column starts in row 1. as Match find the row within the array and Address demands the row in the sheet. Jan . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value
Great. Thank you both.
Jan Bernard Liengme wrote: I hate to be pedantic, Mike, but the formulas we each suggested do NOT need to be array entered. Bernard "Mike H" wrote in message ... Hi, here's another way with an ARRAY formula =INDEX(A2:A20,MATCH(MAX(B2:B20),B2:B20,0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jkrons" wrote: I have this formula: =OFFSET(INDIRECT(ADDRESS(MATCH(MAX(B1:B26),B1:B26, 0),2)),0,-1) It looks up the value one cell left of the largest value in B1:B26, and it works OK, but 1) Can it be done in an easier way? 2) It only works if the column starts in row 1. as Match find the row within the array and Address demands the row in the sheet. Jan . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding * | Excel Discussion (Misc queries) | |||
Finding non-zero value | Excel Discussion (Misc queries) | |||
Finding Max or Min Value | Excel Discussion (Misc queries) | |||
finding the "end" | New Users to Excel | |||
finding the last value | Excel Discussion (Misc queries) |