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

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

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!

Tagged:

Comments

  • L@123[email protected] ✭✭✭✭✭

    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[email protected] ✭✭✭✭✭

    NP

  • 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 = [email protected])

    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.

  • Chris...

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Tamara Which solution are you looking for?

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    Try adjusting your customer number reference

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

  • 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 NewcomePaul 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?

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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

Sign In or Register to comment.