Use VLOOKUP to find multiple values in an array

VLOOKUP will only find the first value in an array for the term you search for. If you want to find multiple values in the array for the successive times that the searched for value appears, you can do so with the help of Excel's COUNTIF formula.

In this example in columns M through O we have shipping dates, destinations, and products. In columns A and B we have a list of shipping dates and destinations. We want to find each time a shipment was made on the date listed in column A and see if one of the shipments was made to the destination in column B.

In column L, we enter this formula:

=M2&COUNTIF($M$2:$M2,M2)

The COUNTIF formula searches for how many times a value appears in column M upwards to row 2, and then adds the result to the end of the date. And so, each of the dates is numbered and unique values are generated.

In column C we enter this formula:

=IFNA(VLOOKUP($A2&COLUMNS($C$1:C1),$L$2:$O$3000,3,0),"")

VLOOKUP searches for the date in column A. Enter the range to columns L to O at the end of the formula. We use '3' to return the data in the third column in this array - the city. The value in COLUMNS refers to column in which the formula is entered.

Pull the formula in C2 to the right, (press CTRL + R) and the multiple cities shipped to on the dates listed in column A will be generated in the columns to the right.