How to use match function to lookup more than one possible value

[lar9149]

Hi all,

I have a set of data where I want to know the match of more than one value, specifically. To give an example lets say I have this

1 10
2 12
5 13
6 19
7 17
1 11
2 15
6 3


And I want to use index along with match to return the values that have 1,2 in front of them...which would be 10,12, 11, 15...match seems to only allow me to put in 1 value at a time, so how do I get around this?

[alansidman]

Look at this link

http://eimagine.com/how-to-return-mu...ch-or-vlookup/

[Phuocam]

Try this ...

=IFERROR(INDEX(B:B,SMALL(IF(($A$1:$A$8=$D$2)+($A$1:$A$8=$E$2),ROW($A$1:$A$8),""),ROWS($D$4:D4))),"")

Enter with Ctrl+Shift+Enter.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	1	10		1	10
3	2	12		2	12
4	5	13			11
5	6	19			15
6	7	17
7	1	11
8	2	15
9	6	3
10

This array formula** entered in E2:

=IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(MATCH(A$2:A$9,D$2:D$3,0)),ROW(A$2:A$9)),ROWS(E$2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.