Bottom to Top Index Match with multiple Criteria

[Scotster]

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

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

=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

 

[DRSteele]

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]

Book1
	A	B	C	D
1				INFOH
2
3	VALUE	INFO	DATE
4	ValueA	INFOA	Date1
5	ValueA	INFOB	Date2
6	ValueA	INFOC	Date3
7	ValueA	INFOD
8	ValueA	INFOE	Date4
9	ValueA	INFOF	Date5
10	ValueA	INFOG	Date6
11	ValueA	INFOH
12	ValueA	INFOI
13	ValueA	INFOJ
Sheet24

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

[/FONT]

 

[Scotster]

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.

	A	B	C	D
1				INFOH
2
3	VALUE	INFO	DATE
4	ValueA	INFOA	Date1
5	ValueA	INFOB	Date2
6	ValueA	INFOC	Date3
7	ValueA	INFOD
8	ValueA	INFOE	Date4
9	ValueA	INFOF	Date5
10	ValueA	INFOG	Date6
11	ValueA	INFOH
12	ValueA	INFOI
13	ValueA	INFOJ

<tbody>
</tbody>

Sheet24​

Worksheet Formulas Cell Formula 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>

=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