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!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Mackers

Well-known Member
Joined
Nov 4, 2013
Messages
536
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
 

asmall4627

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

Watch MrExcel Video

Forum statistics

Threads
1,122,817
Messages
5,598,275
Members
414,221
Latest member
MD1222

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
Top