How to get a lookup (Vlookup or Index Match) to return multiple values?

gregoc43
gregoc43
edited 12/09/19 in Formulas and Functions

I'm essentially trying to have one cell populate with text values from multiple different cells based on a certain criteria.  

What I really wish I had is a JOINIF function, if that explains my problem better.  

Thanks in advance!

Tags:
«1

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    Join(Collect())

    https://help.smartsheet.com/function/collect

     

    My favorite formula in smartsheet. One thing that mixes people up in collect is the input format. It goes

     

    Collect(Return Range, Criteria Range 1, Criteria 1, Criteria Range 2, Criteria 2...)

    The ranges can be across columns or rows, and can even be variable if you play around with the formulas. (I recommend staying away from that until you are very comfortable with how it works)

  • That did it! Thank you so much!

  • L_123
    L_123 ✭✭✭✭✭✭
  • Reference Deadline 1 Deadline 2 Deadline 3 Deadline 4

    I have the situation where I have multiple rows of data all with a unique reference. For each row there are multiple deadlines among multiple other pieces of information.

    On a separate sheet I have in the first column linked in the unique reference numbers. I now need to get Smartsheet to return the next date after today on the row where the reference number matches. 

    Would this be a VLOOKUP with MIN(COLLECT(....))? Does the VLOOKUP get built into the MIN(COLLECT(...) as a second criterion? Or is there no need to use a VLOOKUP for this?

    =MIN(COLLECT(date column range on source sheet, date column range on source sheet, >TODAY(), entire range on source sheet, entire range on source sheet, reference on source sheet = reference@row)

    Would it be better to identify the row with the matching reference first before collecting the lowest date after today in that row?

    Thank you for any help.

  • Tamara
    Tamara ✭✭✭✭✭

    Chris...

    Did you ever determine the correct answer? I have a similar situation and have looked all over for a solution.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Tamara Which solution are you looking for?

  • Tamara
    Tamara ✭✭✭✭✭

    Paul,

    I am new to Smartsheet, and am looking to create a formula to reference customer orders. I've been working with Index/ Match. The customer will be identified by their customer number. If a customer has multiple orders, each order number will be unique. I need to pull the information for my team off the master page and to a simplified page. I wrote an INDEX/MATCH formula that works on the same page. When I use the same formula referencing the master page using the (reference another page option), the formula only works for the first reference. The next reference shows the same as the first reference, instead of showing the next customer order number. The first formula shown below works on the static page but when I re-write it using the "reference another page" option, it doesn't work. I have also tried to use VLOOKUP but with no success. I would greatly appreciate any help with this. Thank You, Tamara


    STATIC PAGE (WORKING) FORMULA =INDEX([J Number 2]1:[J Number 2]4, MATCH([J Number (MAIN)]7, [Customer Number]1:[Customer Number]4, 0))


    NON-WORKING FORMULA REFERENCING ANOTHER PAGE  =INDEX({Range 1},MATCH(Customer Number1,{Range 2},0))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try adjusting your customer number reference

    =INDEX({Range 1}, MATCH([Customer Number]@row, {Range 2}, 0))

  • Tamara
    Tamara ✭✭✭✭✭

    Paul,

    Thank you very much for your help. I'm still struggling to find a solution. My same page Index/Match formula has a glitch when the Customer Number changes. It then repeats the last correct instance, instead of finding the next correct instance. Would you be so kind to evaluate my example below and recommend the best solution to meet my goal of pulling all orders for a specific Customer to a simplified page for my team.

    I've been working to solve trying INDEX/MATCH, VLOOKUP, and JOIN/CONNECT, FIND, but have not found a workable result. I signed up for Smartsheet On Demand training with hopes to learn Smartsheet. I've painstakingly become all to familiar with the terms, "Incorrect Argument Set", "Invalid Operation", and "UNPARSABLE". Any and all help is very very much appreciated. Thank you so much, Tamara.

    Simplified Master Sheet Example - Goal being to pull all orders for a specific Customer to a simplified page.

    For example, Customer 4401 would produce four separate rows on another sheet, showing J0001, J0002, J0004, J0006, and J0008.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Would the data be changing once it is pulled to the other sheet? What is the purpose of the data being pulled? Is it to run calculations on or is it more for a simplified view? How exactly do you determine that the data needs pulled?

  • Tamara
    Tamara ✭✭✭✭✭

    Hi Paul,

    Thanks for the prompt response.

    The data won't change once it's pulled over. The data pulled is Order specific and will be view-only for staff to reference so they can't see confidential data or make changes on the master sheet. A report won't work because the Master Sheet is shared. No calculations needed, just a simple view. Orders will be split between staff and each simplified sheet will be customer specific.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    A report should be able to work. Why does the sharing make it so it won't?

  • Tamara
    Tamara ✭✭✭✭✭

    Hi Paul,

    Thank-you very much for your effort. I ended up solving my question by using a hidden COUNTIF column with a Join to create a Unique Job number that I was then able to pull using an Index/Match.

    I found one of your other posts about how you learned Smartsheet and had to stick with it until you figured it out. I was grateful for the post. I am in a similar situation, to where you were at the beginning. Finally had some some success and things are starting to make more sense.


    Thanks again!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Tamara That's great that you figured out a working solution! 👍️

    Some type of unique identifier per row is always a big help when trying to pull data across sheets like that. It helps you get really specific with exactly which row you want to pull from.

    I am glad one of my other posts was able to resonate with you. Out of curiosity... Would you happen to remember which post it was?

    And finally (even though you already have a working solution)... The reason I had asked about the report and sharing is because if you publish a report, the source sheet does not have to be shared.

  • @Tamara I am facing a similar problem as you did. Seems like you managed to find your way around, can you please help me with that? If you could show your sheet and the formula that you devised to get the task done, that would be really great.

    Much thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!