INDEX/ MULTIPLE MATCH LOOKUP... Grab closest low value from only one array

[bwrobo]

I've been searching for a solution and racking my brain for a while now... and I think (relatively speaking), this is a simple solution that I'm trying to make harder than it is.

I have a standard routing for parts in the format of....

Part#	Process #
Part1	100
Part1	200
Part1	300
Part1	400
Part2	150
Part2	175
Part2	205
Part2	300
Part3	100
Part3	200
Part3	500

If a part is defective new processes are added for it to correct that. So if a Part1 fails at process 200, then it moves to process 205. I have a list of active serial numbers that are defective and need to find the last regular process that was completed for tracking purposes.

Serial	Part	Process	Comments
1	Part1	205	Damaged Seal
2	Part3	165	Incorrect O.D.

I appreciate any help, thank you. I've been back and forth through formulas all day looking for solutions.

[jason.b75]

Using the headers from your sample table in place of ranges, as I can't see which columns your data is in.

Please Login or Register  to view this content.

[bwrobo]

Jason.b75, thank you for your help. The code you provided wasn't exactly what I needed. It provided me the Largest Value of the division. (True/True = 1).

However, it did get my head straight and lead me on the right thought process to solve the issue

Assuming Part # is column A, and Process is Column B....

Please Login or Register  to view this content.

Important things I to note...

1. You have to specify the exact range for your lookup, with the data I was using, I was unable to generalize and use (A:A) for an entire column.

2. If you are pulling from another formula, you will have to use the VALUE function to make any numeric value work. In my case, the process was calculated from another formula, so a direct cell reference would not work until I surrounded it with VALUE to convert it to a number.

[jason.b75]

That was my error, I missed part of the formula.

Please Login or Register  to view this content.

You shouldn't need to wrap your formula in the VALUE function unless you're extracting a number from a text string, or using text functions to split a number.
Data imported from another program might be formatted as text when it is imported, if that is the case then it would be preferable to convert it at the source using text to columns instead of using the value function.

With excel 2019 or office 365, you can use entire columns in a different formula,

Please Login or Register  to view this content.

but excel 2010 does not support this. Instead, you would need to use dynamic named ranges with one of the array methods.

[AliGW]

Please note the correct way to mark a thread as solved is to select Thread Tools from the menu link above and mark this thread as SOLVED. I have done this for you this time.