vlookup

[ali zaib]

we use vlookup for match value but vlookup match only 1st value
can an option available in excel that if a number repeat many time vlookup match with all

 

[Aladin Akyurek]

we use vlookup for match value but vlookup match only 1st value
can an option available in excel that if a number repeat many time vlookup match with all

A2:B10 houses a table of interest and E2 a look up value of interest.

E3:

=COUNTIF(A2:A10,E2)

E4: List of Results

E5, control+shift+enter (CSE), not just enter, and copy down:

=IF(ROWS(E$5:E5)<=E$3,INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=E$2,
 ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(E$5:E5))),"")

CSE means: Press down the control and shift keys together while you hit the enter key.

 

[T. Valko]

we use vlookup for match value but vlookup match only 1st value
can an option available in excel that if a number repeat many time vlookup match with all

See this...

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3