| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match question.
Hi All,
I have been using Index Match formulas for a while, but this one instance has me stumped. Instead of an array of elements I have a matrix, and the Match function does not work. Here is how my data is in the source sheet: Row 1: Label1 Label2 Label3 Row 2: A B C Row 3: D E Row 4: F G In another summary sheet I have Cloumn1 Column 2 A Need function to return Label1 B Need function to return Label2 C Need function to return Label3 D Need function to return Label1 E Need function to return Label2 F Need function to return Label1 G Need function to return Label2 Is it possible to use Vlookup for the function I need in Column 2? I need a function to search through the matrix and return the label above the found value. Thanks in advance! Stan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match question.
On 2 Aug, 14:04, Stan wrote:
Hi All, I have been using Index Match formulas for a while, but this one instance has me stumped. Instead of an array of elements I have a matrix, and the Match function does not work. Here is how my data is in the source sheet: Row 1: Label1 Label2 Label3 Row 2: A B C Row 3: D E Row 4: F G In another summary sheet I have Cloumn1 Column 2 A Need function to return Label1 B Need function to return Label2 C Need function to return Label3 D Need function to return Label1 E Need function to return Label2 F Need function to return Label1 G Need function to return Label2 Is it possible to use Vlookup for the function I need in Column 2? I need a function to search through the matrix and return the label above the found value. Thanks in advance! Stan As always, lots of methods exist (I know I've read some of them) but Microsoft themselves have a method... http://support.microsoft.com/kb/275170 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match question.
Thank you for your reply.
I do not think that the example on that page solves my problem though - It explains how to look up an element on two values using & on two arrays. I need to return a label that the elemnt is under, I am not searching on two criteria, only one. " wrote: On 2 Aug, 14:04, Stan wrote: Hi All, I have been using Index Match formulas for a while, but this one instance has me stumped. Instead of an array of elements I have a matrix, and the Match function does not work. Here is how my data is in the source sheet: Row 1: Label1 Label2 Label3 Row 2: A B C Row 3: D E Row 4: F G In another summary sheet I have Cloumn1 Column 2 A Need function to return Label1 B Need function to return Label2 C Need function to return Label3 D Need function to return Label1 E Need function to return Label2 F Need function to return Label1 G Need function to return Label2 Is it possible to use Vlookup for the function I need in Column 2? I need a function to search through the matrix and return the label above the found value. Thanks in advance! Stan As always, lots of methods exist (I know I've read some of them) but Microsoft themselves have a method... http://support.microsoft.com/kb/275170 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match question.
On 2 Aug, 14:32, Stan wrote:
Thank you for your reply. I do not think that the example on that page solves my problem though - It explains how to look up an element on two values using & on two arrays. I need to return a label that the elemnt is under, I am not searching on two criteria, only one. " wrote: On 2 Aug, 14:04, Stan wrote: Hi All, I have been using Index Match formulas for a while, but this one instance has me stumped. Instead of an array of elements I have a matrix, and the Match function does not work. Here is how my data is in the source sheet: Row 1: Label1 Label2 Label3 Row 2: A B C Row 3: D E Row 4: F G In another summary sheet I have Cloumn1 Column 2 A Need function to return Label1 B Need function to return Label2 C Need function to return Label3 D Need function to return Label1 E Need function to return Label2 F Need function to return Label1 G Need function to return Label2 Is it possible to use Vlookup for the function I need in Column 2? I need a function to search through the matrix and return the label above the found value. Thanks in advance! Stan As always, lots of methods exist (I know I've read some of them) but Microsoft themselves have a method... http://support.microsoft.com/kb/275170- Hide quoted text - - Show quoted text - Ok, lets try this version - it assumes your data is in A2:C4 and is an array formula =OFFSET(A1,0,MAX(IF(A2:C4=A9,COLUMN(A2:C4),0))-1) entered with Ctrl Shift Enter - A9 in this instance is the value I'm trying to match |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match question.
Thats genious. Your formula works, many thanks.
stan " wrote: On 2 Aug, 14:32, Stan wrote: Thank you for your reply. I do not think that the example on that page solves my problem though - It explains how to look up an element on two values using & on two arrays. I need to return a label that the elemnt is under, I am not searching on two criteria, only one. " wrote: On 2 Aug, 14:04, Stan wrote: Hi All, I have been using Index Match formulas for a while, but this one instance has me stumped. Instead of an array of elements I have a matrix, and the Match function does not work. Here is how my data is in the source sheet: Row 1: Label1 Label2 Label3 Row 2: A B C Row 3: D E Row 4: F G In another summary sheet I have Cloumn1 Column 2 A Need function to return Label1 B Need function to return Label2 C Need function to return Label3 D Need function to return Label1 E Need function to return Label2 F Need function to return Label1 G Need function to return Label2 Is it possible to use Vlookup for the function I need in Column 2? I need a function to search through the matrix and return the label above the found value. Thanks in advance! Stan As always, lots of methods exist (I know I've read some of them) but Microsoft themselves have a method... http://support.microsoft.com/kb/275170- Hide quoted text - - Show quoted text - Ok, lets try this version - it assumes your data is in A2:C4 and is an array formula =OFFSET(A1,0,MAX(IF(A2:C4=A9,COLUMN(A2:C4),0))-1) entered with Ctrl Shift Enter - A9 in this instance is the value I'm trying to match |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match question.
On 2 Aug, 16:16, Stan wrote:
Thats genious. Your formula works, many thanks. stan " wrote: On 2 Aug, 14:32, Stan wrote: Thank you for your reply. I do not think that the example on that page solves my problem though - It explains how to look up an element on two values using & on two arrays. I need to return a label that the elemnt is under, I am not searching on two criteria, only one. " wrote: On 2 Aug, 14:04, Stan wrote: Hi All, I have been using Index Match formulas for a while, but this one instance has me stumped. Instead of an array of elements I have a matrix, and the Match function does not work. Here is how my data is in the source sheet: Row 1: Label1 Label2 Label3 Row 2: A B C Row 3: D E Row 4: F G In another summary sheet I have Cloumn1 Column 2 A Need function to return Label1 B Need function to return Label2 C Need function to return Label3 D Need function to return Label1 E Need function to return Label2 F Need function to return Label1 G Need function to return Label2 Is it possible to use Vlookup for the function I need in Column 2? I need a function to search through the matrix and return the label above the found value. Thanks in advance! Stan As always, lots of methods exist (I know I've read some of them) but Microsoft themselves have a method... http://support.microsoft.com/kb/275170-Hide quoted text - - Show quoted text - Ok, lets try this version - it assumes your data is in A2:C4 and is an array formula =OFFSET(A1,0,MAX(IF(A2:C4=A9,COLUMN(A2:C4),0))-1) entered with Ctrl Shift Enter - A9 in this instance is the value I'm trying to match- Hide quoted text - - Show quoted text - I should PROBABLY mention that I didn't put in anything to cater for a NON match!!! Not sure if this is important, at the moment you will get a REF error with a non matching value |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match question.
" wrote: On 2 Aug, 16:16, Stan wrote: Thats genious. Your formula works, many thanks. stan " wrote: On 2 Aug, 14:32, Stan wrote: Thank you for your reply. I do not think that the example on that page solves my problem though - It explains how to look up an element on two values using & on two arrays. I need to return a label that the elemnt is under, I am not searching on two criteria, only one. " wrote: On 2 Aug, 14:04, Stan wrote: Hi All, I have been using Index Match formulas for a while, but this one instance has me stumped. Instead of an array of elements I have a matrix, and the Match function does not work. Here is how my data is in the source sheet: Row 1: Label1 Label2 Label3 Row 2: A B C Row 3: D E Row 4: F G In another summary sheet I have Cloumn1 Column 2 A Need function to return Label1 B Need function to return Label2 C Need function to return Label3 D Need function to return Label1 E Need function to return Label2 F Need function to return Label1 G Need function to return Label2 Is it possible to use Vlookup for the function I need in Column 2? I need a function to search through the matrix and return the label above the found value. Thanks in advance! Stan As always, lots of methods exist (I know I've read some of them) but Microsoft themselves have a method... http://support.microsoft.com/kb/275170-Hide quoted text - - Show quoted text - Ok, lets try this version - it assumes your data is in A2:C4 and is an array formula =OFFSET(A1,0,MAX(IF(A2:C4=A9,COLUMN(A2:C4),0))-1) entered with Ctrl Shift Enter - A9 in this instance is the value I'm trying to match- Hide quoted text - - Show quoted text - I should PROBABLY mention that I didn't put in anything to cater for a NON match!!! Not sure if this is important, at the moment you will get a REF error with a non matching value No problem - I fixed that with a simple if(iserror(blah),"Nata",Blah) The real trick is figguring out how to handle more than one occurence. Right now i just counif and return "more that one occurence" in the output cell. Thanks again |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index match question | Excel Discussion (Misc queries) | |||
| Index/Match Question | Excel Discussion (Misc queries) | |||
| Index/match question. | Excel Discussion (Misc queries) | |||
| Index/Match question Need help! | Excel Worksheet Functions | |||
| Match + Index(?) Question | Excel Discussion (Misc queries) | |||
Linear Mode
