software

A B C D E
1 3 6 9 12
2 5 SAM
3 10
4 15
4 20

How do I search for the intersection of quot;5quot; and quot;9quot; to get quot;SAMquot;. I cannot
use a vlookup because the column i am searching changes.

www.contextures.com/xlFunctio...ml#IndexMatch3--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;sjlquot; gt; wrote in message
...
gt;
gt; A B C D E
gt; 1 3 6 9 12
gt; 2 5 SAM
gt; 3 10
gt; 4 15
gt; 4 20
gt;
gt; How do I search for the intersection of quot;5quot; and quot;9quot; to get quot;SAMquot;. I
gt; cannot
gt; use a vlookup because the column i am searching changes.
Hi,

Try the following index and match formula

=index(A1:E5,match(C1,A1:E1,0),match(A2,A1:A5,0))

Regards,

quot;sjlquot; wrote:

gt;
gt; A B C D E
gt; 1 3 6 9 12
gt; 2 5 SAM
gt; 3 10
gt; 4 15
gt; 4 20
gt;
gt; How do I search for the intersection of quot;5quot; and quot;9quot; to get quot;SAMquot;. I cannot
gt; use a vlookup because the column i am searching changes.

Thanks, i've got it working now.
sjl

quot;Peo Sjoblomquot; wrote:

gt; www.contextures.com/xlFunctio...ml#IndexMatch3
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;sjlquot; gt; wrote in message
gt; ...
gt; gt;
gt; gt; A B C D E
gt; gt; 1 3 6 9 12
gt; gt; 2 5 SAM
gt; gt; 3 10
gt; gt; 4 15
gt; gt; 4 20
gt; gt;
gt; gt; How do I search for the intersection of quot;5quot; and quot;9quot; to get quot;SAMquot;. I
gt; gt; cannot
gt; gt; use a vlookup because the column i am searching changes.
gt;
gt;
gt;

Works great, thank you.
sjl

quot;Ashish Mathurquot; wrote:

gt; Hi,
gt;
gt; Try the following index and match formula
gt;
gt; =index(A1:E5,match(C1,A1:E1,0),match(A2,A1:A5,0))
gt;
gt; Regards,
gt;
gt; quot;sjlquot; wrote:
gt;
gt; gt;
gt; gt; A B C D E
gt; gt; 1 3 6 9 12
gt; gt; 2 5 SAM
gt; gt; 3 10
gt; gt; 4 15
gt; gt; 4 20
gt; gt;
gt; gt; How do I search for the intersection of quot;5quot; and quot;9quot; to get quot;SAMquot;. I cannot
gt; gt; use a vlookup because the column i am searching changes.