Index Match Multiple Rows

[hshone]

Hi all,

I'm trying to get a formula that can return multiple data across various rows.

Id	Room	Value
123	Bedroom	Red
123	*******	Blue
512	*******	Blue
21	Bedroom	Green
21	*******	Red

<tbody>
</tbody>

If I have a list of of IDs i want to look up against it will return the values for each ID in a different col, in short get it to look something like this.

Id	Bedroom	*******
123	Red	Blue
512		Blue
21	Green	Red

<tbody>
</tbody>

Any ideas, at the moment I'm using a mass of pivot tables, not ideal.

Thanks in advance.

 

[cyrilbrd]

Hi,
try:

Excel 2013
	A	B	C	D	E	F	G	H
1	Id	Room	Value			Id	Bedroom	*******
2	123	Bedroom	Red			123	Red	Blue
3	123	*******	Blue			512		Blue
4	512	*******	Blue			21	Green	Red
5	21	Bedroom	Green
6	21	*******	Red
hshone

Cell Formulas
Range	Formula
G2	{=IFERROR(INDEX($C$2:$C$6,MATCH(1,($F2=$A$2:$A$6)*(G$1=$B$2:$B$6),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hshone]

Hi,

Many thanks for getting back to me. I can't seem to make it work, even when setting up as an array formula. Any reason why it might not be working, i'm using excel 2010 would that make a difference?

 

[cyrilbrd]

No, it should work fine, please kindly describe what exactly is not working...

 

[hshone]

Hi Sorry I had a space behind the letter making it not work.

The next problem I'm facing is that is it possible to extend this over a whole col. at the moment I'm having to enter the array function into each cell to make it work and I can't select all the cells and press Ctrl + shift+ enter.

Thanks once again for your great help.

 

[cyrilbrd]

You need not enter CSE for each cell, rather in the first formula, adjust range to fit your model, enter as CSE, copy, choose all cells below (exclude first one) and paste. Repeat copy paste for adjacent columns.