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

Sandcastor

Board Regular
Joined
May 14, 2012
Messages
97
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:

IkWdrn7.png

https://pasteboard.co/IkWdrn7.png

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


CompanyWell IDProjectJob TypeSample
Received
Sample
Range
Received
Date
Shipped
Date
Samples
Billed
Samples
Remain
COMMENTS
Bravo568813794C0117-12-1317-12-1917-12-19109
Bravo568823794C0117-12-1317-12-1917-12-1900
Charlie534394013C1317-12-1417-12-2117-12-21207
Charlie535103238C1017-12-1717-12-2117-12-21209
Alpha552803659C-O10----17-12-2117-12-21101
Alpha552813659C-O10----17-12-2117-12-21103
Bravo568813794C02-0317-12-14 -
17-12-21
17-12-2818-01-02107
Juliet55311----C-O13-2017-12-06 -
08-01-02
18-01-0418-01-05207
Alpha55795----C-O07-1017-12-29 -
18-01-04
18-01-0818-01-08100
Tango55237----S38-50----18-01-1618-01-2270----
Romeo558363837C05-0718-01-06 -
18-01-10
18-01-2518-01-262013
Romeo467113837G18-01-2418-01-2518-01-260(26)

<tbody>
</tbody>
</div>
IkWdrn7.png


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!
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Use MATCH(whatever,Range,1) instead of ending with 0. It will give the row of the last one!
 
Upvote 0
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))
 
Upvote 0
Solution
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.
Code:
=LOOKUP(2,1/(ZZRefColumn=ZZRnum),ROW(ZZRefColumn))
I have never used Vectors before. Looks like my next frontier.

Thanks, all.
And so quickly too.
 
Upvote 0
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))
 
Upvote 0
The formula:
Code:
[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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top