Bottom to Top Index Match with multiple Criteria

Scotster

New Member
Joined
May 29, 2017
Messages
25
Hi there,


Looking for some help with an array formula I'm trying to come up with. I have a dataset which is mostly auto-populated. There are a few exceptions where the cells are not populated, and never will be, thus I wish to find the last row matching a certain set of criteria.
The formula I have for now is...


Code:
=INDEX('IW39'!B:B,MATCH(1,('IW39'!A:A=B2)*('IW39'!C:C=""),0),1)


It's successful in finding the first empty cell in Column while matching B2 with column A.


The trouble I have is that there are occasions where values in C are intentionally skipped and left blank, with further data filled in below. Therefore, I would like to get the index value by searching from the bottom of the sheet.


I thought I could do something like....


Code:
=INDEX('IW39'!B:B,MATCH(2,1/(('IW39'!A:A=B2)*('IW39'!D:D="")),0),1)


I don't have enough experience with this method though and I've only ever done it with simple match statements with single criteria.
Am I on the right path?


ValueA INFOA Date1
ValueA INFOB Date2
ValueA INFOC Date3
ValueA INFOD
ValueA INFOE Date4
ValueA INFOF Date5
ValueA INFOG Date6
ValueA INFOH
ValueA INFOI
ValueA INFOJ


In the above example I want to find INFOH.My formula successfully finds INFOD. Unfortunately sometimes INFOD will be the correct answer, when there is no subsequent dates filled in.


Many thanks :)
 
Last edited:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,331
Office Version
  1. 365
Platform
  1. Windows
This formula in D1 will find the last INFO that has a Date, skip down one row and report the INFO below. If the last INFO has a date, it reports a message.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #E2EFDA;;">INFOH</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="background-color: #FFF2CC;;">VALUE</td><td style="background-color: #FFF2CC;;">INFO</td><td style="background-color: #FFF2CC;;">DATE</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">ValueA</td><td style=";">INFOA</td><td style=";">Date1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">ValueA</td><td style=";">INFOB</td><td style=";">Date2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">ValueA</td><td style=";">INFOC</td><td style=";">Date3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">ValueA</td><td style=";">INFOD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">ValueA</td><td style=";">INFOE</td><td style=";">Date4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">ValueA</td><td style=";">INFOF</td><td style=";">Date5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">ValueA</td><td style=";">INFOG</td><td style=";">Date6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">ValueA</td><td style=";">INFOH</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">ValueA</td><td style=";">INFOI</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">ValueA</td><td style=";">INFOJ</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet24</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">B4:B13,MAX(<font color="Green">(<font color="Purple">C4:C13<>""</font>)*(<font color="Purple">ROW(<font color="Teal">C4:C13</font>)-ROW(<font color="Teal">C4</font>)+1</font>)</font>)+1</font>),"NOT MEANINGFUL"</font>)</td></tr></tbody></table></td></tr></table><br />[/FONT]
 

Scotster

New Member
Joined
May 29, 2017
Messages
25
This formula in D1 will find the last INFO that has a Date, skip down one row and report the INFO below. If the last INFO has a date, it reports a message.

ABCD
1INFOH
2
3VALUEINFODATE
4ValueAINFOADate1
5ValueAINFOBDate2
6ValueAINFOCDate3
7ValueAINFOD
8ValueAINFOEDate4
9ValueAINFOFDate5
10ValueAINFOGDate6
11ValueAINFOH
12ValueAINFOI
13ValueAINFOJ

<tbody>
</tbody>
Sheet24

Worksheet Formulas
CellFormula
D1=IFERROR(INDEX(B4:B13,MAX((C4:C13<>"")*(ROW(C4:C13)-ROW(C4)+1))+1),"NOT MEANINGFUL")

<tbody>
</tbody>

<tbody>
</tbody>


Hi there,

Many thanks for the response. I believe that will only work with one value though... ValueA? It's a fairly vast dataset that I'm using with 1000s of values in column A with corresponding data.

I think I've got it working. I'm finished till Monday to try it properly but I believe I have solved it by researching what the 1, 0, -1 switch does for the Match formula.

ValueLocationDate EnteredValueCurrent Location
12345Loc101/01/201912345Loc8
12345Loc202/01/2019678910Loc10
12345Loc303/01/2019111213Loc9
12345Loc404/01/2019
12345Loc505/01/2019
12345Loc6
12345Loc707/01/2019
12345Loc8
12345Loc9
678910Loc101/01/2019
678910Loc202/01/2019
678910Loc303/01/2019
678910Loc404/01/2019
678910Loc505/01/2019
678910Loc6
678910Loc707/01/2019
678910Loc808/01/2019
678910Loc909/01/2019
678910Loc10
111213Loc101/01/2019
111213Loc202/01/2019
111213Loc303/01/2019
111213Loc404/01/2019
111213Loc505/01/2019
111213Loc6
111213Loc707/01/2019
111213Loc808/01/2019
111213Loc9
111213Loc10

<tbody>
</tbody>

Code:
=INDEX(B:B,MATCH(2,1/((A:A=E2)*(C:C<>"")),1)+1,1)


Essentially finds the last entered date value for a given Value, adds one to the row match and then reports the Location value of said row.

Fingers crossed :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,715
Messages
5,597,726
Members
414,169
Latest member
Preston_Cleric

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