Formula to look into table and return values if condition is met

[bjnockle]

Looking for formula to look into table A2:D102 and pull values into F2:F102 If condition is met. The two (2) conditions are Year in J1 and Names in M1:M26. Year condition in J1 is manually entered and can be changed to any year. Names condition is also manually entered and can be changed based on the Year in consideration. For this sample file, Year 1993 (J1) is used and the names for this year is copied into M1:M26.

If a name in M1:M26 is in D2:D102 and the Sales City and Sales ID are not the same as the Sales City and Sales ID for the name in M1:M26, the formula should return that name in M1:M26 into F2:F102. For example, Murp, Chris (in M10. Sales City for Murp, Chris is Hiss and Sales ID 45234 in Table A2:D102) is in D6 and the Sales City and Sales ID for Murp, Chris in D6 are Kirt-Wise and 21222 which are unique. Formula should return Murp, Chris in F6. If the Sales City for Murp, Chris (SALES CITY in N1:N26 is the same Sales City in B2:B102 for the name being considered in M1:M26, the formula should not return a value since the Sales City are the same AND NOT UNIQUE.

Array formula and non-array welcome. Non-array preferred.

See sample file.

Thanks.

[FlameRetired]

Really? 499 posts in 7 years?

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[bjnockle]

FlameRetired: I will make adjustment to my request title shortly. Thank you for bringing this to my attention.

[bjnockle]

FlameRetired: Title adjusted. Thanks.

[bebo021999]

Try in F2:

Please Login or Register  to view this content.

Drag down
P/S: Name in D60 is different from M2

[bjnockle]

bebo021999: Excellent solution. Works! Thanks alot.