Reverse Index/Match

JAN71866

New Member
Joined
Feb 1, 2019
Messages
3
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Feb 1, 2019
Messages
3
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.


image.jpg
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680

ADVERTISEMENT

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 :
Code:
=MIN(IF($B$2:$M$32="Test",ROW($B$2:$M$32)))-1

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

Two array formulas for your End date :

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

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

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

JAN71866

New Member
Joined
Feb 1, 2019
Messages
3
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 :
Code:
=MIN(IF($B$2:$M$32="Test",ROW($B$2:$M$32)))-1

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

Two array formulas for your End date :

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

Code:
=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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Once you have tested the Array Formulas ...

Feel free to share your comments ...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

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