Possible Alternative to index match formula?

[STEVENS3010]

Hi all,

I'm currently using an index match formula to look up a range of data and to populate this into another sheet. The issue I have is the index match formula only returns one row of information. Is there an alternative formula I could use to return all rows which contain my lookup value?

 

[samitnair]

Please post the formula with an example to help u better

 

[Fluff]

Can you post an example of your data & expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JamesCanale]

Do I have this right? The filter function is nice, it outputs an array:

MrExcelPlayground.xlsm
	A	B	C
1	Name	State	Amount
2	Jamie	NY	50
3	Fred	NJ	40
4	Jamie	CT	60
5	Harry	NV	70
6	Fred	FL	110
7	Fred	AL	20
8	Jamie	AK	50
9	Jamie	PA	60
10	Fred	LA	70
11	Harry	WA	20
12
13	Jamie
14	Jamie	NY	50
15	Jamie	CT	60
16	Jamie	AK	50
17	Jamie	PA	60
Sheet45

Cell Formulas
Range	Formula
A14:C17	A14	=FILTER(A2:C11,A2:A11=A13,"")
Dynamic array formulas.

 

[STEVENS3010]

Do I have this right? The filter function is nice, it outputs an array:

MrExcelPlayground.xlsm
	A	B	C
1	Name	State	Amount
2	Jamie	NY	50
3	Fred	NJ	40
4	Jamie	CT	60
5	Harry	NV	70
6	Fred	FL	110
7	Fred	AL	20
8	Jamie	AK	50
9	Jamie	PA	60
10	Fred	LA	70
11	Harry	WA	20
12
13	Jamie
14	Jamie	NY	50
15	Jamie	CT	60
16	Jamie	AK	50
17	Jamie	PA	60
Sheet45

Cell Formulas
Range	Formula
A14:C17	A14	=FILTER(A2:C11,A2:A11=A13,"")
Dynamic array formulas.

Thanks for the replies. I’ve managed to sort what I was looking for with another formula. Appreciate you replying though