Match and index functions based selection parts

[chandrankrishnan]

Hi all,Good day,
For the attached sheet,i tried with match,index formula and lookup formula too.But by output doesnot meet my requirements.

for the green marked part to be searched(D55342E07B1E00R,value:1k) and its corresponding Locations to shown without duplicates.then next parts etc..,,,,, like this.

can anyone guide me this.

[Lupp]

First of all I would suggest you learn abut relative vs absolute addressing. Your formula in column A primarily suffers from wrongly addressing the ranges relative regarding the rows.

The more fundamental problem is that you expect to get all the matches, not only the first one. There is no standard function supporting this directly.
If you want to do it by functions, most likely applying AutoCalculate, there are two ways:
-1- Design user functions capable of supporting your needs. You cannot get the functionality with recorded macros.
-2- Dedicate a few helper columns to prepare the filtering.
Both these ways are demonstrated in the attached file. Concerning the first wy I didn't write a specialized function but used multi-porpose functions from my toolbox (not first quality yet). If you prefer to not permit my user code to run, only the second solution will work.

Without "macros" and some interaction instead an "Advanced Filter" might be the means of choice. (I personally rarely use the tool.)

What you meant by the "split output" (range C6:G15) I did not understand at all.

[chandrankrishnan]

Dear Lupp,
Thanks for the reply.I will use the 2nd way to get the output.

[Lupp]

You are welcome.

However, you should consider to replace the previous second sheet with the only one I attach here. The difference is only in the formulas in column R where I had missed to reduce the range in which to search for matches in the appropriate way. It's about the subtrahend on the fourth parameter position of OFFSET().

[chandrankrishnan]

Dear Lupp,

Thanks for the reply and your good response.