INDEX MATCH non-contiguous range

[Samuelkmh]

I have a table that looks like this. I have two variables that I need to select for the index match, namely Field and Portfolio.
I have selected portfolio y and Field 1. I need to obtain value 2, but using index match, i can keep obtaining value 1. It doesnt work when I change my column range from "Category A: Field 6" to "Category b: Field 5". I understand it has to do with the Match Column Range, that the column range has to intersect with the row range for the index match to work. However, is there a way to obtain just value 2?

Portfolio x Portfolio y Portfolio z
Category a
Field 1 Value 1
Field 2
Field 3


Category b
Field 1 Value 2
Field 4
Field 5

Category c
Field 1
Field 3
Field 6

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want? If not, please try to explain further and give some more examples to demonstrate what you need.

Excel 2016
	A	B	C	D	E	F	G
1		Portfolio x	Portfolio y	Portfolio z		Portfolio	Portfolio y
2	Category a					Field	Field 1
3	Field 1		Value 1			Result	Value 2
4	Field 2
5	Field 3
6
7
8	Category b
9	Field 1		Value 2
10	Field 4
11	Field 5
12
13	Category c
14	Field 1
15	Field 3
16	Field 6
Get Value

Cell Formulas
Range	Formula
G3	=INDEX(B2:D16,AGGREGATE(14,6,(ROW(B2:B16)-ROW(B2)+1)/((A2:A16=G2)*(INDEX(B2:D16,0,MATCH(G1,B1:D1,0))<>"")),1),MATCH(G1,B1:D1,0))

 

[Samuelkmh]

Hi Peter! this is exactly what I need. Thanks so much for your help! Just wondering if it could be translated to a VBA code. Somehow when I tried it, I think there is a syntax error somewhere.

 

[Samuelkmh]

Hi, there is a new template of the spreadsheet given to me. Below is the format.
What I need to do is the following:
I need to be able to look up an extra sub selection. If i select total cash and field Y, i need value 5. If i select total equities as a sub selection and Field Y, I should get Value 1 as a result. The range for the first column starts at Total Equities, nothing above that. The row I need to start from is row 4, where the portfolio group is. There are 2 empty columns between each portfolio.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Portfolio A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Portfolio B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Master Selection[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Portfolio Group:[/TD]
[TD]PortfolioB[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sub selection:[/TD]
[TD]Total Cash[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Portfolio Group:[/TD]
[TD]PortfolioA[/TD]
[TD][/TD]
[TD][/TD]
[TD]Portfolio Group:[/TD]
[TD]PortfolioB[/TD]
[TD][/TD]
[TD][/TD]
[TD]Field:[/TD]
[TD]Field Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Result:[/TD]
[TD]Value 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Equities[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Value 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Value 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Cash[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field W[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Value 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Value 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Alternatives[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Value 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Value 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]