Really challenging lookup situation

Hello every one,
I got this really challenging lookup situation.
First this is sample table layout:
__A____B__C____D___E_____F____G__
_________1ST__2ND__3RD__2ND__1ST_
name1__________X_________X________
name2_____X______________X_____X__
name3__1__X_________X__________X__
name4_______X_________X____X______
name5__1__X_________X__________X__
name6____________X_________X______
name7__1__X_________X____X________
name8____________X____X___________
name9__1____X______________X______
I want formula which will return list of names from col. A, if there is no number in col.B and there is “X” in 1ST or 2nd col(s)., but ignore 3rd col.
Please help me I am losing sleep over this one.
I do understand that this is calling for multicell array formula, and I also don’t want to create any additional columns to achieve desired result.
Thank you,
Guntars

Please post a sample file with the same info that you've displayed and the desired results.

Here is the sample file,

Originally Posted by guntar

Hello every one,
I got this really challenging lookup situation.
First this is sample table layout:
__A____B__C____D___E_____F____G__
_________1ST__2ND__3RD__2ND__1ST_
name1__________X_________X________
name2_____X______________X_____X__
name3__1__X_________X__________X__
name4_______X_________X____X______
name5__1__X_________X__________X__
name6____________X_________X______
name7__1__X_________X____X________
name8____________X____X___________
name9__1____X______________X______
I want formula which will return list of names from col. A, if there is no number in col.B and there is “X” in 1ST or 2nd col(s)., but ignore 3rd col.
Please help me I am losing sleep over this one.
I do understand that this is calling for multicell array formula, and I also don’t want to create any additional columns to achieve desired result.
Thank you,
Guntars

One option would be to use a filter column.
In K3

Please Login or Register  to view this content.

Copy down as far as necessary
With a cell within your table or in the new column, go to Data Filer > Autofilter
In the dropdown in the newly created column, choose True
Highlight and copy the names
Paste the names somewhere.
In the dropdown in the newly created column, choose (All).
Remove the filter and delete filter column.

This process can be automated with a macro if you like.

The following assumes there is no data below your table and your destination for the names is in the "O" column.

Please Login or Register  to view this content.