How to return more than one value with multiple serach values with vlookup

07/01/21
Answered - Pending Review

Hi,

I have two columns ID and C. Based on a vlooup formula in C with the search value in ID and need to show the value in C.

My Question here is when I need to show multiple values based on multiple IDs. Example: If ID1=Result1 ID2=Result2 and ID3=Result3 and in ID column I write 1 2 or something like this and i want to show Result1 and Result2 in column C.

Note: The ID and C values are based in another Sheet.

Thank you


Answers

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Andre Ramos

    I hope you're well and safe!

    Not sure I follow!

    You can look up each part separately and then add them together with something like +" - "+.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hello Andrée,


    Yes something like that, can you show me an example of that formula?

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Andre Ramos

    Here's an advanced example.

    =IF(JOIN(DISTINCT(COLLECT([Column Name 1]:[Column Name 1], [Parent Row]:[Parent Row], [Child Row]@row))) <> "", PARENT() + "Test" + "-" + [Column Name 2]@row + "-" + INDEX([Column Name 1]:[Column Name 1], MATCH([Child Row]@row, [Parent Row]:[Parent Row], 0)))

    Make sense?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Yes, but in your example you do it for the rows and columns in the same sheet and i need to pull the ID and C data from other sheet.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Andre Ramos

    Here's another example.

    =IFERROR((INDEX(COLLECT([email protected], [Column Name 1]@row, >0, VLOOKUP([Column Name 2]@row, {Pricing Example}, 2, 0), VLOOKUP([Column Name 2]@row, {Pricing Example}, 2, 0)), 1)), "") + CHAR(10) + IF([Column Name 2]@row = "", "", [Column Name 2]@row + " " + "(Example Text " + SpecificCell$26 + " " + [PriceExample 2]@row + ")")

    Better?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • edited 07/02/21

    @Andrée Starå

    Sorry but I couldn't understand this advanced formulas, I've started working with smartsheet just in a couple weeks. Can you please explain me step by step what this formulas are doing?

    And please give me examples with my real context problem to an easier understanding.

    Sheet 1:

    I have ID's and some names in column C (sensitive data hided). This is where the central information are localized.

    Sheet 2:

    I want to write multiple ID's in a cell and this ID values appear in the column C. Imagine this scenario.

    Sheet1:

    Columns - ID C

    Cell 1 - 18 name1

    Cell 2 - 19 name2

    Cell 3 - 20 name3

    I want in sheet2 something like this:

    Columns - ID C

    (Same cell)18 19 20 name1 name2 name3

    Thank You.

  • @Andrée Starå

    Sorry, where i write same cell, it is same row.

    Thanks

Sign In or Register to comment.