Vlookup that returns an array as a value

0
votes

So I'm trying to set up a vlookup that will look up a value of a list, but it returns an array instead of a value.

For example:

  • if A then return the range A1:C1
  • if B then return the range A2:C2

as an array.

I'm trying to use this in the networkdays function for the holidays section so I can look up a business and return the range that has all the holidays the business is closed for.

I've tried to just create a column with the array of dates in it. Such as Column B is {=C1:E1} where the holidays are listed in cells C1, D1, and E1, but this only ever returns the first value instead of the whole list in the array.

NETWORKDAYS("Start Date","End Date",VLOOKUP("Business",Sheet1!A:B,2,))

Where Sheet1 is the sheet containing a list of businesses in column A and Column B is the array of holidays listed in C:E

Sheet 1 set up

   A         B         C        D       E 

Business {=C1:E1}  1/1/2019 5/1/2019 7/4/2019
1
excelvlookup

1 Answers

0
votes

Don't use VLOOKUP it will only return one value use INDEX:

NETWORKDAYS("Start Date","End Date",INDEX(Sheet1!C:E,MATCH("Business",Sheet1!A:A,0),0))

this will now return all the values in C:E on the row where "Business" is found in Column A.