Hey there
The following problem I am working on since days:
Beginning in Tab1 I want to compare if a company in Tab2 exists, that has three similar variables. The result is Variable4. However, the accordance in the three variables are within a certrain range (see file).
The problem is that for each firm various comparable firms exist in Tab2 and I am not sure how to mark or list them all...
There are 2 alternatives for the result:
1. On the one hand all(!) comparable firms should be marked/flagged; and
2. on the other hand it would be helpful, if only the compared company in Tab2 with the lowest value in column L is shown/flagged.
I started with the formula already, but the process is not auotatized since I have to copy and paste each firm from Tab1 to Tab2 in N2 to Q2, for these I have to let excel search in Tab2 the according comparable comps. (I took firm 1 as an example to show what I mean. It finds itself in Tab2 since it is part of the comparable comps (but no true solution), so there is always at least one solution. I wouldn't know any formula that avoids finding it.)
I split the formula into three parts for a better overview to see for which variables the criteria is not sufficient to be part of the comparable firms as a solution.
I can imagine there is an easier solution with matrix or VBA, but I am not able to find out...
The file is attached.
Thanks for any help you can offer!!
Best,
Peter
Bookmarks