Index matches

[gdperry]

Hi Everyone
I'm Glenn, I don't do excel programming much but I need some help on this difficult formuaa..

[gdperry]

=INDEX($E$3:$E$1200,MATCH("Marc Paeuser"&1&"42747"&42749, $A$3:$A$1200&$B$3:$B$1200&$H$3:$H$1200&$J$3:$J$1200,0))
The 42747 and the 42749 are two dates that I want to reference. The first and second dates when referenced both returns the correct information when it matches. I want to add to the formula so if the second date is either equal too or less than 42749 it will also be a match.. I have tried but it doesn't work..please help me with this..
=INDEX($E$3:$E$1200,MATCH("Marc Paeuser"&1&"42747"&<=42749, $A$3:$A$1200&$B$3:$B$1200&$H$3:$H$1200&$J$3:$J$1200,0))

[gdperry]

The purpose of this formula is to take a excel list of work dates and techs and transpose this into a calendar view. I can get the start date and finish date to fill the excel sheet but I cant get the dates in-between to fill in. that's why I need the <= to work with the second date.

[gdperry]

This is my first time using a forum..

[JohnTopley]

Try

=INDEX($E$3:$E$1200,MATCH(1,( $A$3:$A$1200="Marc Paeuser")*($B$3:$B$1200=1)*($H$3:$H$1200=42747)*($J$3:$J$1200<=42749),0))

Enter with Ctrl+Shift+Enter

[gdperry]

OMG You are awesome!!! it works great!! life saver

[JohnTopley]

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[gdperry]

Hi john

Actually this doesn't do what I want.. the formula should return on E if the first date matches or if it matches the second date or earlier

It seems to always match now

[gdperry]

Hi John
I am mistaken. there is one slight error that just showed up.. Maybe I didn't explain myself well. The formula should return from E when the first date matches the last date or any date in-between.. it seems to return on E all the time. Regardless of dates

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[gdperry]

Resource Name Count Demand Description Start Date End Date

Marc Paeuser 0 108s Amelia Infrared Scanning 42745 42745
Marc Paeuser 1 108s Amelia Sub Inspection 42745 42746
Marc Paeuser 2 108s 4th Q DGA/Oil Quality 42745 42747
Marc Paeuser 3 388s Riverside Infrared Scanning 42745 42748
Marc Paeuser 4 388s Riverside Sub Inspection 42745 42749

42744 42745 42746 42747 42748 42749
Mon Tue Wed Thu Fri Sat
#N/A 108s #N/A #N/A #N/A #N/A
Marc P #N/A #N/A #N/A #N/A #N/A #N/A
Marc P #N/A #N/A #N/A #N/A #N/A #N/A
Marc P #N/A #N/A #N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A #N/A #N/A

All work starts on same day 42745.. each consecutive job per day will continue an extra day in the week. By Sat only one job is left.

Hi John
Everything on Monday should be N/A which it is,
Everything on Tuesday should return as all dates are 42745
I added an extra day for each line (0, 1,2,3,4)so entries should show up adding an entry through the week.

[JohnTopley]

Sorry ..need a workbook.

[gdperry]

=INDEX($E$3:$E$1200,MATCH(1,( $A$3:$A$1200="Marc Paeuser")*($B$3:$B$1200=1)*($H$3:$H$1200<=42748)*($J$3:$J$1200>=42748),0))

seems to work so far

[gdperry]

how do I send the work book

[gdperry]

attachement should be there