Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search/Extract Formula
I have 3 columns of non-consecutive data in several rows.
Column 1 has description A, description B, description C, description A, description M, etc. Column 2 has description AA, description AD, description AF, description AD, description AO, etc Column 3 has amounts. I need a formula that searches column 1 for all instances of description A. Based on the smallest value associated with the description A items, I want to place the description of column 2 (description result would be in the same row as the smallest value) into the cell the formula is written in. In the cell next to the formula above, I want to place the value (i.e. the smallest value) that corresponds to the first formula's answer/result. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search/Extract Formula
One way ..
Assuming the 3 source cols are cols A to C, with data expected within rows 1 - 100 In D1 will be entered: description A Place in E1, array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(D1="","",INDEX(B$1:B$100,MATCH(MIN(IF($A$1:$A$ 100=$D1,$C$1:$C$100)),$C$1:$C$100,0))) Copy E1 to F1 E1:F1 will return the required results from cols B and C. In the event of any ties in the minimum values for the description entered in D1, only the "highest up" row values from cols B and C will be returned. As-is, the array formulas in E1:F1 can be copied down to return correspondingly for other descriptions entered in D2, D3, etc Visually check for correct array-entry in E1 before copying across to F1. Look for the curly braces { } which will be inserted by Excel (we don't type-in these braces). Correctly array-entered, it'll appear in the formula bar like this: {=IF(D1="","",...} -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M Moore" wrote: I have 3 columns of non-consecutive data in several rows. Column 1 has description A, description B, description C, description A, description M, etc. Column 2 has description AA, description AD, description AF, description AD, description AO, etc Column 3 has amounts. I need a formula that searches column 1 for all instances of description A. Based on the smallest value associated with the description A items, I want to place the description of column 2 (description result would be in the same row as the smallest value) into the cell the formula is written in. In the cell next to the formula above, I want to place the value (i.e. the smallest value) that corresponds to the first formula's answer/result. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search/Extract Formula
Hi!
Both of these formulas need to be entered as arrays using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(B2:B20,MATCH(MIN(IF(A2:A20="description A",C2:C20)),IF(A2:A20="description A",C2:C20),0)) =MIN(IF(A2:A20="description A",C2:C20)) Biff "M Moore" wrote in message ... I have 3 columns of non-consecutive data in several rows. Column 1 has description A, description B, description C, description A, description M, etc. Column 2 has description AA, description AD, description AF, description AD, description AO, etc Column 3 has amounts. I need a formula that searches column 1 for all instances of description A. Based on the smallest value associated with the description A items, I want to place the description of column 2 (description result would be in the same row as the smallest value) into the cell the formula is written in. In the cell next to the formula above, I want to place the value (i.e. the smallest value) that corresponds to the first formula's answer/result. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search/Extract Formula
Oops, sorry, errata to suggested formula in E1:
=IF(D1="","",INDEX(B$1:B$100,MATCH(MIN(IF($A$1:$A$ 100=$D1,$C$1:$C$100)),$C$1:$C$100,0))) The front part of the formula should have read as: =IF($D1="","", (with the dollar sign to fix it to col D) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search/Extract Formula
E1:F1 will return the required results from cols B and C. In the event of
any ties in the minimum values for the description entered in D1, only the "highest up" row values from cols B and C will be returned. ...........A..............B................C 1........B..............X................2 2........A..............Y................2 3........A..............Z................2 Your formulas will return X...2 when it should return Y...2. Biff "Max" wrote in message ... One way .. Assuming the 3 source cols are cols A to C, with data expected within rows 1 - 100 In D1 will be entered: description A Place in E1, array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(D1="","",INDEX(B$1:B$100,MATCH(MIN(IF($A$1:$A$ 100=$D1,$C$1:$C$100)),$C$1:$C$100,0))) Copy E1 to F1 E1:F1 will return the required results from cols B and C. In the event of any ties in the minimum values for the description entered in D1, only the "highest up" row values from cols B and C will be returned. As-is, the array formulas in E1:F1 can be copied down to return correspondingly for other descriptions entered in D2, D3, etc Visually check for correct array-entry in E1 before copying across to F1. Look for the curly braces { } which will be inserted by Excel (we don't type-in these braces). Correctly array-entered, it'll appear in the formula bar like this: {=IF(D1="","",...} -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M Moore" wrote: I have 3 columns of non-consecutive data in several rows. Column 1 has description A, description B, description C, description A, description M, etc. Column 2 has description AA, description AD, description AF, description AD, description AO, etc Column 3 has amounts. I need a formula that searches column 1 for all instances of description A. Based on the smallest value associated with the description A items, I want to place the description of column 2 (description result would be in the same row as the smallest value) into the cell the formula is written in. In the cell next to the formula above, I want to place the value (i.e. the smallest value) that corresponds to the first formula's answer/result. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search/Extract Formula
Good catch, Biff. Thanks!
Make it in E1, array-entered as: =IF($D1="","",INDEX(B$1:B$100,MATCH(MIN(IF($A$1:$A $100=$D1,$C$1:$C$100)),IF($A$1:$A$100=$D1,$C$1:$C$ 100)),0)) Then copy E1 to F1, fill down Note that the better alternative to set it up is given in Biff's earlier response -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Biff" wrote in message ... E1:F1 will return the required results from cols B and C. In the event of any ties in the minimum values for the description entered in D1, only the "highest up" row values from cols B and C will be returned. ..........A..............B................C 1........B..............X................2 2........A..............Y................2 3........A..............Z................2 Your formulas will return X...2 when it should return Y...2. Biff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |