INDEX-MATCH last matching entry in a list - no VBA?

[Sandcastor]

Hello all again. It's been a while.
As usual, I have scoured, and cannot find an existing answer. If an answer exists, please redirect, otherwise, I need some help.

Situation: I keep a growing database (excel) of samples, for multiple customers, that I have cataloged and shipped for my job. There are many fields per shipment, but see below for a pared-down version. I also have a label-making sheet that references the database, and am building an at-a-glance report for the customers as well. The source data looks like this:

https://pasteboard.co/IkWdrn7.png

EDIT: Got something here:
<div id="PROTECHNICS SAMPLES LOG SHEET_2253" align="center
xublishsource=" excel"="">Sample Example


[TABLE="width: 1274"]
<tbody>[TR]
[TD="class: xl682253"]Company[/TD]
[TD="class: xl692253, width: 176"]Well ID[/TD]
[TD="class: xl692253, width: 138"]Project[/TD]
[TD="class: xl692253, width: 138"]Job Type[/TD]
[TD="class: xl692253, width: 138"]Sample
Received[/TD]
[TD="class: xl692253, width: 138"]Sample
Range[/TD]
[TD="class: xl692253, width: 127"]Received
Date[/TD]
[TD="class: xl692253, width: 138"]Shipped
Date[/TD]
[TD="class: xl692253, width: 138"]Samples
Billed[/TD]
[TD="class: xl692253, width: 188"]Samples
Remain[/TD]
[TD="class: xl692253, width: 177"]COMMENTS[/TD]
[/TR]
[TR]
[TD="class: xl672253"]Bravo[/TD]
[TD="class: xl702253"]56881[/TD]
[TD="class: xl702253"]3794[/TD]
[TD="class: xl672253"]C[/TD]
[TD="class: xl712253"]01[/TD]
[TD="class: xl712253"]17-12-13[/TD]
[TD="class: xl722253"]17-12-19[/TD]
[TD="class: xl722253"]17-12-19[/TD]
[TD="class: xl732253"]10[/TD]
[TD="class: xl732253"]9[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl672253"]Bravo[/TD]
[TD="class: xl702253"]56882[/TD]
[TD="class: xl702253"]3794[/TD]
[TD="class: xl672253"]C[/TD]
[TD="class: xl712253"]01[/TD]
[TD="class: xl712253"]17-12-13[/TD]
[TD="class: xl722253"]17-12-19[/TD]
[TD="class: xl722253"]17-12-19[/TD]
[TD="class: xl732253"]0[/TD]
[TD="class: xl732253"]0[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl672253"]Charlie[/TD]
[TD="class: xl702253"]53439[/TD]
[TD="class: xl702253"]4013[/TD]
[TD="class: xl672253"]C[/TD]
[TD="class: xl712253"]13[/TD]
[TD="class: xl712253"]17-12-14[/TD]
[TD="class: xl722253"]17-12-21[/TD]
[TD="class: xl722253"]17-12-21[/TD]
[TD="class: xl732253"]20[/TD]
[TD="class: xl732253"]7[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl672253"]Charlie[/TD]
[TD="class: xl702253"]53510[/TD]
[TD="class: xl702253"]3238[/TD]
[TD="class: xl672253"]C[/TD]
[TD="class: xl712253"]10[/TD]
[TD="class: xl712253"]17-12-17[/TD]
[TD="class: xl722253"]17-12-21[/TD]
[TD="class: xl722253"]17-12-21[/TD]
[TD="class: xl732253"]20[/TD]
[TD="class: xl732253"]9[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl742253"]Alpha[/TD]
[TD="class: xl702253"]55280[/TD]
[TD="class: xl702253"]3659[/TD]
[TD="class: xl672253"]C-O[/TD]
[TD="class: xl712253"]10[/TD]
[TD="class: xl712253"]----[/TD]
[TD="class: xl722253"]17-12-21[/TD]
[TD="class: xl722253"]17-12-21[/TD]
[TD="class: xl732253"]10[/TD]
[TD="class: xl732253"]1[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl742253"]Alpha[/TD]
[TD="class: xl702253"]55281[/TD]
[TD="class: xl702253"]3659[/TD]
[TD="class: xl672253"]C-O[/TD]
[TD="class: xl712253"]10[/TD]
[TD="class: xl712253"]----[/TD]
[TD="class: xl722253"]17-12-21[/TD]
[TD="class: xl722253"]17-12-21[/TD]
[TD="class: xl732253"]10[/TD]
[TD="class: xl732253"]3[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl672253"]Bravo[/TD]
[TD="class: xl702253"]56881[/TD]
[TD="class: xl702253"]3794[/TD]
[TD="class: xl672253"]C[/TD]
[TD="class: xl712253"]02-03[/TD]
[TD="class: xl712253"]17-12-14 -
17-12-21[/TD]
[TD="class: xl722253"]17-12-28[/TD]
[TD="class: xl722253"]18-01-02[/TD]
[TD="class: xl732253"]10[/TD]
[TD="class: xl732253"]7[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl672253"]Juliet[/TD]
[TD="class: xl702253"]55311[/TD]
[TD="class: xl702253"]----[/TD]
[TD="class: xl672253"]C-O[/TD]
[TD="class: xl712253"]13-20[/TD]
[TD="class: xl712253"]17-12-06 -
08-01-02[/TD]
[TD="class: xl722253"]18-01-04[/TD]
[TD="class: xl722253"]18-01-05[/TD]
[TD="class: xl732253"]20[/TD]
[TD="class: xl732253"]7[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl672253"]Alpha[/TD]
[TD="class: xl702253"]55795[/TD]
[TD="class: xl702253"]----[/TD]
[TD="class: xl672253"]C-O[/TD]
[TD="class: xl712253"]07-10[/TD]
[TD="class: xl712253"]17-12-29 -
18-01-04[/TD]
[TD="class: xl722253"]18-01-08[/TD]
[TD="class: xl722253"]18-01-08[/TD]
[TD="class: xl732253"]10[/TD]
[TD="class: xl732253"]0[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl742253"]Tango[/TD]
[TD="class: xl702253"]55237[/TD]
[TD="class: xl702253"]----[/TD]
[TD="class: xl672253"]S[/TD]
[TD="class: xl712253"]38-50[/TD]
[TD="class: xl712253"]----[/TD]
[TD="class: xl722253"]18-01-16[/TD]
[TD="class: xl722253"]18-01-22[/TD]
[TD="class: xl732253"]70[/TD]
[TD="class: xl732253"]----[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl742253"]Romeo[/TD]
[TD="class: xl702253"]55836[/TD]
[TD="class: xl702253"]3837[/TD]
[TD="class: xl672253"]C[/TD]
[TD="class: xl712253"]05-07[/TD]
[TD="class: xl712253"]18-01-06 -
18-01-10[/TD]
[TD="class: xl722253"]18-01-25[/TD]
[TD="class: xl722253"]18-01-26[/TD]
[TD="class: xl732253"]20[/TD]
[TD="class: xl732253"]13[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="class: xl742253"]Romeo[/TD]
[TD="class: xl702253"]46711[/TD]
[TD="class: xl702253"]3837[/TD]
[TD="class: xl672253"]G[/TD]
[TD="class: xl712253"][/TD]
[TD="class: xl712253"]18-01-24[/TD]
[TD="class: xl722253"]18-01-25[/TD]
[TD="class: xl722253"]18-01-26[/TD]
[TD="class: xl732253"]0[/TD]
[TD="class: xl732253"](26)[/TD]
[TD="class: xl712253"][/TD]
[/TR]
[TR]
[TD="width: 199"][/TD]
[TD="width: 176"][/TD]
[TD="width: 138"][/TD]
[TD="width: 138"][/TD]
[TD="width: 138"][/TD]
[TD="width: 138"][/TD]
[TD="width: 127"][/TD]
[TD="width: 138"][/TD]
[TD="width: 138"][/TD]
[TD="width: 188"][/TD]
[TD="width: 177"][/TD]
[/TR]
</tbody>[/TABLE]
</div>

Currently, I have the label and report sheets reference a specific row number for INDEX-MATCH purposes. Specifically I will Type "120" to reference all of the data on line 120. You know how that works.

What I would like to do instead is to type the WellID associated with the job I'm working on (in the example 56881) and have the INDEX-MATCH refer to the LAST entry in the list that matches. I would prefer to have this done without VBA, as the database is macro-free for ease of use and ease of reference for multiple read-only users.

Is there any solution to this? I expect it will be a new combination of functions I haven't seen before. Thanks!

 

[BobUmlas]

Use MATCH(whatever,Range,1) instead of ending with 0. It will give the row of the last one!

 

[Eric W]

If the Well ID you're looking up is in M2, and the column with Well IDs is B, then this will return the last matching row number, which you can use in an INDEX/MATCH:

=LOOKUP(2,1/(B3:B20=M2),ROW(B3:B20))

 

[Sandcastor]

Use MATCH(whatever,Range,1)

That gives the last row in the entire table. Not what I need here, but I'll keep it. I will find a use for it at some point, I'm sure.

=LOOKUP(2,1/(B3:B20=M2),ROW(B3:B20))

I just tried this, and I have even already tried replacing the absolute references with named ranges. Works like a hot dang.

=LOOKUP(2,1/(ZZRefColumn=ZZRnum),ROW(ZZRefColumn))

I have never used Vectors before. Looks like my next frontier.

Thanks, all.
And so quickly too.

 

[BobUmlas]

Actually, for this to work the data must be in sequence, and it needs to exist, so modify the formula to
=IF(ISNA(MATCH(whatever,Range,0)),"Not found",MATCH(whatever,Range,1))

 

[BobUmlas]

Or this:
MAX((B3:B20=M2)*ROW(B3:B20))

 

[Sandcastor]

The formula:

[COLOR=#333333]MAX(([/COLOR][COLOR=#333333][COLOR=#333333]B3:B20[/COLOR][/COLOR][COLOR=#333333]=[/COLOR][COLOR=#333333][COLOR=#333333]M2[/COLOR][/COLOR][COLOR=#333333])*ROW([/COLOR][COLOR=#333333][COLOR=#333333]B3:B20[/COLOR][/COLOR][COLOR=#333333]))[/COLOR]

gave me either 0 or NA depending on which kind of reference I used.

I also tried scrambling the order of the data, and the Eric W's formula still returned the correct reference.

And thanks for the ISNA, but our entries will be specific enough to not need it. I'm the only one with write access, everyone else is Read-Only :P
Hopefully someone finds it useful. Could also IFERROR, no?