Index Match question.

[Stan]

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

 

[aidan.heritage]

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

 

[Stan]

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.

 

[aidan.heritage]

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.







- 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

 

[Stan]

Thats genious. Your formula works, many thanks.
stan

 

[aidan.heritage]

Thats genious. Your formula works, many thanks.
stan






- 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

 

[Stan]

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