Reverse Index/Match

[JAN71866]

I have a vertical calendar I use to set up my yearly audits. I want to create a table with a function that will automatically return the start date and end date of each audit based on the first and last time the audit name appears (i.e., Test Wall). Is there an easy solution to creating this - I looked up reverse index/match function, but not sure if that's the one I would use.

 

[James006]

Hi,

Could you describe your worksheet structure ...

Are you after locating the Row with the first Match AND the Row with the last Match ...?

 

[Gerald Higgins]

Hi, welcome to the board.

What's a vertical calendar ?
Is it all the dates in the year, in a single vertical column ?
If yes, then finding the start data of the audit is straightforward, if the audit name is entered against every date.
This is not REVERSE Index / Match, it's the standard index / match.

Something like
=index(A1:A365,match(C1,B1:B365,0))

where A1:A365 contains all the dates in the year
C1 contains the name of the specific audit you are intersted in
B1:B365 contains the data of which audit is taking place each day.

Finding the end date might be a bit more tricky.
If they always take the same amount of time - say a full week - then it's easy.

 

[JAN71866]

Hi,

Could you describe your worksheet structure ...

Are you after locating the Row with the first Match AND the Row with the last Match ...?

A vertical calendar (what it's called by the excel template) has the months as headers across the top and the days as the first column going down 1 through 31. I set up my schedules with the first and last days of my audits and want to return the month and day of any one start and end dates for an audit to put out in a table to copy and past into an email.

 

[James006]

Hello,

Given the structure you are using ... you will need two formulas for each date ....
in order to return Day (row) and Month (column) ...
Say your project is named : Test ...

Two Array Formulas for your Start date :

=MIN(IF($B$2:$M$32="Test",ROW($B$2:$M$32)))-1

=MIN(IF($B$2:$M$32="Test",COLUMN($B$2:$M$32)))-1

Two array formulas for your End date :

=MAX(IF($B$2:$M$32="Test",ROW($B$2:$M$32)))-1

=MAX(IF($B$2:$M$32="Test",COLUMN($B$2:$M$32)))-1

Hope this will help you ... ' auditing ' ...

 

[JAN71866]

Hello,

Given the structure you are using ... you will need two formulas for each date ....
in order to return Day (row) and Month (column) ...
Say your project is named : Test ...

Two Array Formulas for your Start date :

=MIN(IF($B$2:$M$32="Test",ROW($B$2:$M$32)))-1

=MIN(IF($B$2:$M$32="Test",COLUMN($B$2:$M$32)))-1

Two array formulas for your End date :

=MAX(IF($B$2:$M$32="Test",ROW($B$2:$M$32)))-1

=MAX(IF($B$2:$M$32="Test",COLUMN($B$2:$M$32)))-1

Hope this will help you ... ' auditing ' ...

I used this in my spreadsheet, but it came back with #Value .

 

[James006]

You are welcome ...

BUT ... With an Array Formula ...

Instead of the standard Enter Key ...

You need to use simultaneously the three keys : Control Shift Enter

 

[James006]

Once you have tested the Array Formulas ...

Feel free to share your comments ...