Specify range in an index formula to a specific criteria

[Ghesselink]

Hello,

I made a table (see E2:J6) based on the data from A1:A12.
To get the data for this, I made a formula that gets the name from a specific category.
This formula is: IFERROR(INDEX($C$1:$C$4,MATCH(F2, $B$1:$B$4,0)),"")

If I drag this row to the right (so e.g. from F3:J3), the data gets filled in automatically.
However, when I drag it down I have to change the $C$1:$C$4 and $B$1:$B$4 values manually (for example to $B$5:$B$6 for the name in Category 1 - case B).

Is there a way to do this in a formula so I can drag it down. I thought by matching the range to the value in the E column, but I cannot find on how to do this.

Thanks in advance,

 

[Oscar Cronquist]

Try this formula in cell F3:

=INDEX($C$1:$C$12,MATCH($E3&F$2,$A$1:$A$12&$B$1:$B$12,0))

 

[Ghesselink]

It does not work unfortunately, I get a #VALUE ! error

 

[Oscar Cronquist]

I am sorry, I forgot. It is an array formula.

1. Paste formula to cell F13
2. Press and hold CTRL + SHIFT
3. Press Enter

 

[Peter_SSs]

You can also use a standard-entry format like this, copied across and down.

(I also suggest that you follow the link in my signature block below so that in future you can provide sample data in a form that can be copied for testing. Most helpers don't want to be bothered typing sample data so if you can make it easy for them you will get more potential helpers)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	A	Category 1	Name 1
2	A	Category 2	Name 2			Category 1	Category 2	Category 3	Category 4	Category 5
3	A	Category 3	Name 3		A	Name 1	Name 2	Name 3	Name 4
4	A	Category 4	Name 4		B			Name 6		Name 5
5	B	Category 5	Name 5		C	Name 7		Name 8	Name 9
6	B	Category 3	Name 6		D	Name 10		Name 11	Name 12
7	C	Category 1	Name 7
8	C	Category 3	Name 8
9	C	Category 4	Name 9
10	D	Category 1	Name 10
11	D	Category 3	Name 11
12	D	Category 4	Name 12
Fill Table

 

[Ghesselink]

I did your suggestion and it works now.

I will post the data and code in that way the next time, I can imagine it is easier to help with that indeed.

Thank you both for your help!

 

[Peter_SSs]

Thank you both for your help!

You are welcome.