# Using INDEX MATCH or VLOOKUP on Multiple Columns to Result an Object Based on a Date

#### asmall4627

##### New Member
Hi, I posted this earlier, but realized my title was a bit confusing.

I'm attempting to make a schedule where Objects 1-30 in Column A have predicted inspection dates in an array G2:P:31, with possibly overlapping dates of inspection. I wanted to see if I could use INDEX MATCH to search and find specific dates within the array and return the corresponding Object that must be inspected on that date, which I want to place into separate sheets for each month of the year. Essentially, am I able to use INDEX MATCH to search multiple columns for a date, and return a value that is in a single column to the left of those dates?

I've listed an example below:

 Objects Class Initial Date Predicted Dates Predicted Dates Cont. A 2 5/5/15 6/6/15 7/7/15 B 3 5/13/15 7/13/15 9/13/15 C 2 5/7/15 8/7/15 11/7/15 D 2 5/7/15 8/7/15 11/7/15

<tbody>
</tbody>

So I'd like to write a function that looks at the array from below the 1st "Predicted Dates" column to the end of the 2nd "Predicted Dates" column and returns the specific Object that corresponds to that date, which can then be placed into another sheet that lists every day in column A, with the Object to be inspected in column B next to the date, and then any other concurrent Object that is also scheduled for that date being either concatenated with the 1st value or being placed in column C next to the previous value.

I know that's a lot, but I was just hoping to see if I could get any insight or feedback.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi asmall4627

You have a set of date ranges, and you want to find the corresponding object for a specific date such that the date range relating to that object encompasses your specified date? From your data it looks like some dates may return multiple objects, e.g. any date corresponding to object C would also correspond to object D as they have the same date ranges.

The first solution that comes to mind is to have a separate sheet (let's say Sheet2) with dates by day in column A, then headers of "A", "B", "C" and "D" in columns B, C, D and E respectively. You can then use a formula to return a 1 or a 0 for each day and object to see if the object's date range is within that day. One such formula would be:

=countifs(Sheet1!\$A\$2:\$A\$5, Sheet2!B\$1, Sheet1!\$D\$2:\$D\$5, "<="&Sheet2!\$A2, Sheet1!\$E\$2:\$E\$5, ">="&Sheet2!\$A2)

Where the objects are in Sheet1!\$A\$2:\$A\$5, the letter "A" is in Sheet2!B\$1, the start date is in Sheet1!\$D\$2:\$D\$5, the required date is in Sheet2!\$A2 and the end date is in Sheet1!\$E\$2:\$E\$5.

Hope that helps

Mackers

Hi Mackers,

Thanks for the response! I'll definitely try your method, and give some feedback afterwards as to how it went! I appreciate your help!

Replies
1
Views
168
Replies
4
Views
401
Replies
1
Views
63
Replies
2
Views
201
Replies
1
Views
749

1,218,942
Messages
6,145,352
Members
450,610
Latest member
TheEnginerd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back