Returning multiple values using Vlookup in excel

Question

I have an excel sheet set up to automatically calculate meetings per day by day of the week. I would like to write a formula to return all dates I have a meeting scheduled (comma separated preferably), but I am having some difficulty. Using Vlookup, I can only get it to return the first date.

For example, here is what my data looks like:

         A                                 B                            C
 Initial Meetings          Follow-up Meetings            Date
         1                                1                       7/29/2015
         0                                1                       7/30/2015
         1                                1                       7/31/2015
         0                                0                       8/1/2015
         0                                0                       8/2/2015

I would like to write a formula to return "7/29/2015, 7/31/2015" in one cell, and "7/29/2015, 7/30/2015, 7/31/2015" in another, but I seem to be stuck.

Answer 1

You can't do this with vLookup.

This can be done relatively easily in a VB script, but it would affect portability as many if not most users disable macros by default and in many cases users are prevented from using Macros because their company disables them and makes it policy that users should not use them.

If you are OK with Macros, you can put the following into a new module and then use =MultiVlookup(lookup_value,table_array, col_index_num) in the same way as you'd use vlookup and it should give you a comma separated list of multiple matches:

Public Function MultiVlookup(find_value, search_range, return_row)
  Dim myval ' String to represent return value (comma-separated list)
  Dim comma ' Bool to represent whether we need to prefix the next result with ", "
  comma = False
  'Debug.Print find_value.value, return_row
  For Each rw In search_range.Rows ' Iterate through each row in the range
    If rw.Cells(1, 1).value = find_value Then ' If we have found the lookup value...
      If comma Then  ' Add a comma if it's not the first value we're adding to the list
        myval = myval + ", "
      Else
        comma = True
      End If
      myval = myval + Str(rw.Cells(1, return_row).value)
    End If
  Next
  MultiVlookup = myval
End Function

This may not be the cleanest way of doing it, and it isn't a direct copy of vlookup (for instance it does not have a fourth "range lookup" argument as vlookup does), but it works for my test:

Finally my original suggestion (in case it helps others - it's not the exact solution to the question) was:

I've not tried it myself, but this link shows what I think you might be looking for.