Looking for a range of cells to be returned...

[semper5]

I am looking for help in the following:

I would like the user to input a value. Then, ideally, Excel would look at that value, look for the matching value in a list, and return a range of cells.

Using MATCH only returns a single cell. Am I on the right track by researching MATCH still?

I am thinking only in theory right now, no working sheet. Just wondering aloud.

 

[semper5]

Ok, here's an example.

 

[p45cal]

In I3:
=VLOOKUP($I$2,$A$2:$E$6,2,FALSE)

In J3:
=VLOOKUP($I$2,$A$2:$E$6,3,FALSE)

In K3:
=VLOOKUP($I$2,$A$2:$E$6,4,FALSE)

In L3:
=VLOOKUP($I$2,$A$2:$E$6,5,FALSE)

 

[semper5]

That's what I went to doing, I was just curious if there was a different way. Thank you for your assistance.

 

[Hui]

If you are using Office 365 you can use Textjoin

=Textjoin(", ",TRUE,VLOOKUP($I$2,$A$2:$E$6,{2,3,4,5},FALSE)) Ctrl+Shift+Enter
will return "1, 5, 6, 7"
if I2 has Emp 1