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

asmall4627

New Member
Joined
Jun 9, 2015
Messages
3
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:

ObjectsClassInitial DatePredicted DatesPredicted Dates Cont.
A25/5/156/6/157/7/15
B35/13/157/13/159/13/15
C25/7/158/7/1511/7/15
D25/7/158/7/1511/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.
Thank you in advance!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi asmall4627

Just to clarify your question:

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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top