# Bottom to Top Index Match with multiple Criteria

#### Scotster

##### New Member
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:

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### DRSteele

##### Well-known Member
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.

#### Scotster

##### New Member
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
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.

 Value Location Date Entered Value Current Location 12345 Loc1 01/01/2019 12345 Loc8 12345 Loc2 02/01/2019 678910 Loc10 12345 Loc3 03/01/2019 111213 Loc9 12345 Loc4 04/01/2019 12345 Loc5 05/01/2019 12345 Loc6 12345 Loc7 07/01/2019 12345 Loc8 12345 Loc9 678910 Loc1 01/01/2019 678910 Loc2 02/01/2019 678910 Loc3 03/01/2019 678910 Loc4 04/01/2019 678910 Loc5 05/01/2019 678910 Loc6 678910 Loc7 07/01/2019 678910 Loc8 08/01/2019 678910 Loc9 09/01/2019 678910 Loc10 111213 Loc1 01/01/2019 111213 Loc2 02/01/2019 111213 Loc3 03/01/2019 111213 Loc4 04/01/2019 111213 Loc5 05/01/2019 111213 Loc6 111213 Loc7 07/01/2019 111213 Loc8 08/01/2019 111213 Loc9 111213 Loc10

<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

Replies
8
Views
168
Replies
4
Views
149
Replies
20
Views
368
Replies
9
Views
510
Replies
7
Views
245

1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

### 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.

### Which adblocker are you using?

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

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