| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and Tables
Can anybody tell me what to do:
I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use this formula: =MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A. Is there another way to get the row number for the cell, containing the maximum value? Jan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match and Tables
MATCH only operates on 1D vectors. C22:H33 is 2D.
=SUMPRODUCT((ROW(C22:H33)-ROW(C22)+1)*(C22:H33=MAX(C22:H33))) This formula will work correctly only if MAX will appear only once in the data. HTH Kostis Vezerides On Nov 11, 2:31*pm, wrote: Can anybody tell me what to do: I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use this formula: =MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A. Is there another way to get the row number for the cell, containing the maximum value? Jan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match and Tables
Hi,
Assuming this is the data in the range D5:E10 1 45 2 67 3 878 10 12 5 23 6 1 In F5, enter the following array formula =IF(OR(D5:E5=MAX($D$5:$E$10)),MAX(D5:E5),""). Copy this formula down to F10. IN cell F12, enter the following formula =MATCH(MAX(D5:E10),F5:F10,0)+COUNTBLANK($F$1:F4) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... Can anybody tell me what to do: I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use this formula: =MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A. Is there another way to get the row number for the cell, containing the maximum value? Jan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match and Tables
Thank you. I used your formula and it worked out fine.
Jan vezerid wrote: MATCH only operates on 1D vectors. C22:H33 is 2D. =SUMPRODUCT((ROW(C22:H33)-ROW(C22)+1)*(C22:H33=MAX(C22:H33))) This formula will work correctly only if MAX will appear only once in the data. HTH Kostis Vezerides On Nov 11, 2:31 pm, wrote: Can anybody tell me what to do: I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use this formula: =MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A. Is there another way to get the row number for the cell, containing the maximum value? Jan |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match and Tables
I used versrid's formula, but thanks anywe
Ashish Mathur wrote: Hi, Assuming this is the data in the range D5:E10 1 45 2 67 3 878 10 12 5 23 6 1 In F5, enter the following array formula =IF(OR(D5:E5=MAX($D$5:$E$10)),MAX(D5:E5),""). Copy this formula down to F10. IN cell F12, enter the following formula =MATCH(MAX(D5:E10),F5:F10,0)+COUNTBLANK($F$1:F4) wrote in message ... Can anybody tell me what to do: I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use this formula: =MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A. Is there another way to get the row number for the cell, containing the maximum value? Jan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match and Tables
I used vezerid's formula, but thanks anyway.
Jan Ashish Mathur wrote: Hi, Assuming this is the data in the range D5:E10 1 45 2 67 3 878 10 12 5 23 6 1 In F5, enter the following array formula =IF(OR(D5:E5=MAX($D$5:$E$10)),MAX(D5:E5),""). Copy this formula down to F10. IN cell F12, enter the following formula =MATCH(MAX(D5:E10),F5:F10,0)+COUNTBLANK($F$1:F4) wrote in message ... Can anybody tell me what to do: I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use this formula: =MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A. Is there another way to get the row number for the cell, containing the maximum value? Jan |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match and Tables
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Jan Kronsell" wrote in message ... I used vezerid's formula, but thanks anyway. Jan Ashish Mathur wrote: Hi, Assuming this is the data in the range D5:E10 1 45 2 67 3 878 10 12 5 23 6 1 In F5, enter the following array formula =IF(OR(D5:E5=MAX($D$5:$E$10)),MAX(D5:E5),""). Copy this formula down to F10. IN cell F12, enter the following formula =MATCH(MAX(D5:E10),F5:F10,0)+COUNTBLANK($F$1:F4) wrote in message ... Can anybody tell me what to do: I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use this formula: =MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A. Is there another way to get the row number for the cell, containing the maximum value? Jan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| hOW TO MATCH DATA CONTAINED IN DIFFERENT TABLES | Excel Discussion (Misc queries) | |||
| Match and Index for Tables | Excel Worksheet Functions | |||
| Nested MATCH with two tables | Excel Worksheet Functions | |||
| How do I match up two tables of data with one column in common | Excel Worksheet Functions | |||
| Match two tables using unique ID number | Excel Discussion (Misc queries) | |||
Linear Mode
