Index/Match to FIND blanks and with partial values

[JTee]

Hi everyone - longtime lurker (I seem to always find my answers on the forum), first time registered user and poster. I don't know if I am all Corona-ed out or if I am just losing my mind, but I have a two pronged problem that I am having trouble solving. I am creating a rollup of three separate reports that come directly out of a database for a client.

Problem 1: One of the reports includes extra data in the same cells with my unique lookup value. I know how to do this in a vlookup (using "*"), but its not working for me in an index/match.
Problem 2: Once I am able to get to the right range solving problem 1 above, I need to offset on that report to the first row in that column with a blank value, then find the corresponding data on that row in several columns to the right. Basically, this dang report has a total line without a label - so the total line is blank in column A. I have tried and failed and I would give up - but this client is very important and I need to make sure they are happy.

Here is an image of my data set on the problematic report:

Note that while the snip above shows the offset to be 2 rows down from the lookup value, that is not always the case - it ranges between two and 30 rows until the next blank in column A.

Here is a snip of the way I need the data from this report rolled up:

Column A is my unique identifier to all other data.

Any and all help on my conundrum is certainly appreciated. Thanks!

 

[KRice]

One of the reports includes extra data in the same cells with my unique lookup value.

What specifically are you trying to match? Is it the Unit number...for example "A1015"?...and is the issue that other text follows A1015?

 

[Toadstool]

Hi JTee,

Are you sure that row column A is empty or does it contain a/some blanks? Let me show using my test data and you can tell me if this looks right.

Report

JTee.xlsx
	A	B	C	D	E	F	G	H	I
1
2	Code/Date								Pre-Payment
3	A1012 - Alf Brown
4	2/2/2020		Payment
5									22.33
6	A1022 - Sally Smith
7	2/2/2020		Payment
8	2/2/2020		Payment
9	2/2/2020		Payment
10									44.55
11	A999 - George Jones
12	2/2/2020		Payment
13	2/2/2020		Payment
14	2/2/2020		Payment
15									55.66
16	A9999 - Susan Hillier
17	2/2/2020		Payment
18									66.77
19
Report

Retrieved number

JTee.xlsx
	A	B	C
1
2	Unit		Pre-Payment
3	A1012		22.33
4	A1022		44.55
5	A9999		66.77
6	A999		55.66
7
Summary

Cell Formulas
Range	Formula
C3:C6	C3	=INDEX(Report!$I$1:$I$9999,AGGREGATE(15,6,ROW(Report!$A$1:$A$9999)/((ROW(Report!$A$1:$A$9999)>MATCH(A3&" *",Report!A:A,0))*(Report!$A$1:$A$9999="")),1))

 

[JTee]

Toadstool - your solution worked like a charm! Is it possible for you to explain the logic in the forula to me so that I understand HOW it is working? Thank you SO MUCH.

 

[Toadstool]

Sure,

AGGREGATE(15,6,ROW(Report!$A$1:$A$9999)/((ROW(Report!$A$1:$A$9999)>MATCH(A3&" *",Report!A:A,0))*(Report!$A$1:$A$9999="")),1))

INDEX(Report!$I$1:$I$9999,
This will retrieve the contents of column I for the row number returned by the AGGREGATE

AGGREGATE(15,6,ROW(Report!$A$1:$A$9999)/
The "15" option says use the AGGREGATE SMALL function to find the nth occurrence, so 1st, 2nd, etc. In this case it's only looking for the 1st that meets criteria because it has hardcoded the "1))" at the end. The ROW(Report!$A$1:$A$9999) says we're going to look at all rows between 1 and 9,999.
The "6" says ignore errors because we're dividing each row number by the criteria after the "/".

((ROW(Report!$A$1:$A$9999)>MATCH(A3&" *",Report!A:A,0))*(Report!$A$1:$A$9999="")),
The MATCH checks for the Unit in the report using a wildcard "*" search. The space in front of the wildcard is in case you have short Unit codes, so "A999 *" will not match "A9999".
The ROW(Report!$A$1:$A$9999)> checks that the row we're looking at is greater than the row with the matching Unit from the MATCH.
The (Report!$A$1:$A$9999="") gives a logical 1 if the row in column A is empty and a logical zero if it's not.
If either/both of these conditions are false they'll return a logical zero so multiplying them together gets zero. If both conditions are true then multiplying them together gives a logical 1. These are divided into the row number from the AGGREGATE so a zero gives a zerodivide which the "6" option ignores. If they return a 1 then you get that row number returned which the INDEX can then retrieve.

 

[JTee]

THANK YOU SO MUCH! This is phenomenal, I ahve never used the aggregate function before, and I love learning new excel tricks. You're the best - you made my day!

 

[Toadstool]

You're welcome!
...but be careful as AGGREGATE can be very powerful and you might end up as an AGGREGATE junkie like me