1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'Ask an Excel Question' started by semper5, Oct 11, 2017 at 6:45 PM.

  1. semper5

    semper5 Member

    Messages:
    33
    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.
  2. semper5

    semper5 Member

    Messages:
    33
    Ok, here's an example.

    Attached Files:

  3. p45cal

    p45cal Well-Known Member

    Messages:
    822
    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 likes this.
  4. semper5

    semper5 Member

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

    Hui Excel Ninja Staff Member

    Messages:
    10,684
    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
    semper5 and Naresh Yadav like this.

Share This Page