| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index/Match ... Repost from 05/10/07
Excel2003 ...
Col A ... Text format ... values alpha-numeric & may repeat Col B ... Text format ... values alpha-numeric & may repeat (note: Col B sorted in Ascending order) (note: length of Col A & B Cell contents & # of repeats is random) Col L ... I am using INDEX/MATCH to find value in Col B, return Value in Col A. Formula working fine except when value in Col B repeats ... Issue is ... My INDEX/MATCH formula is returning 1st value found in Col A (Q5X) ... What I need when a match is found in Col B ... is the value returned from Col A that looks like a number. For the example below ... I wish Col A value of 123 to be returned. Col A ...... Col B Q5X ....... 1A5637 C6Y ........ 1A5637 123 ........ 1A5637 X2Z ........ 1A5637 Thank you for the guidance ... Kha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match ... Repost from 05/10/07
Looks like a number???
=INDEX(Sheet2!A1:A10, (MATCH(1,(ISNUMBER(-Sheet2!A1:A10)*(Sheet2!B1:B10=B1)),0))) Is a number: =INDEX(Sheet2!A1:A10, (MATCH(1,(ISNUMBER(Sheet2!A1:A10)*(Sheet2!B1:B10=B 1)),0))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Ken wrote: Excel2003 ... Col A ... Text format ... values alpha-numeric & may repeat Col B ... Text format ... values alpha-numeric & may repeat (note: Col B sorted in Ascending order) (note: length of Col A & B Cell contents & # of repeats is random) Col L ... I am using INDEX/MATCH to find value in Col B, return Value in Col A. Formula working fine except when value in Col B repeats ... Issue is ... My INDEX/MATCH formula is returning 1st value found in Col A (Q5X) ... What I need when a match is found in Col B ... is the value returned from Col A that looks like a number. For the example below ... I wish Col A value of 123 to be returned. Col A ...... Col B Q5X ....... 1A5637 C6Y ........ 1A5637 123 ........ 1A5637 X2Z ........ 1A5637 Thank you for the guidance ... Kha -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match ... Repost from 05/10/07
Mr Dave ...
Perfect ... Thank you for the Magic ... Kha "Dave Peterson" wrote: Looks like a number??? =INDEX(Sheet2!A1:A10, (MATCH(1,(ISNUMBER(-Sheet2!A1:A10)*(Sheet2!B1:B10=B1)),0))) Is a number: =INDEX(Sheet2!A1:A10, (MATCH(1,(ISNUMBER(Sheet2!A1:A10)*(Sheet2!B1:B10=B 1)),0))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Ken wrote: Excel2003 ... Col A ... Text format ... values alpha-numeric & may repeat Col B ... Text format ... values alpha-numeric & may repeat (note: Col B sorted in Ascending order) (note: length of Col A & B Cell contents & # of repeats is random) Col L ... I am using INDEX/MATCH to find value in Col B, return Value in Col A. Formula working fine except when value in Col B repeats ... Issue is ... My INDEX/MATCH formula is returning 1st value found in Col A (Q5X) ... What I need when a match is found in Col B ... is the value returned from Col A that looks like a number. For the example below ... I wish Col A value of 123 to be returned. Col A ...... Col B Q5X ....... 1A5637 C6Y ........ 1A5637 123 ........ 1A5637 X2Z ........ 1A5637 Thank you for the guidance ... Kha -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Index, Match, Sum ?? | Excel Discussion (Misc queries) | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
| Index / Match? | Excel Worksheet Functions | |||
Linear Mode
