Complex Search using OR/Index/Lookup ??? Trying to create new table as output

[George Drivas]

Trying to Included link to my sample file of tables and detailed description of help needed within but apparently I’m not allowed. Please post or PM me your email or receive a link to the worksheet. Happy to compensate once solved if reasonable.

Basics: search a sheet with 150 fixed rows in two separate columns for an OR match to a criteria input in a cell on another sheet. If match is found in either column, return unique identifier in column A of searched sheet for further processing. Desired output is data across 6 specific columns from the corresponding rows where a match was found in searched sheet. Not looking for a single occurrence of a match but rather all matches in all 150 rows of data. Results are to be output into a new table (or similar format for display) with only the output data and no blank lines for cleanliness and readability. Not sure if I am over complicating this or not. Any help is greatly appreciated.

[MrShorty]

What you are describing sounds more like a "filter" operation, so I would probably start by trying Excel's built in filter utilities.

Autofilter is easier to use, but will only filter the list in place. However, it is easy to copy -> paste the filtered data elsewhere. https://www.wikihow.com/Use-AutoFilter-in-MS-Excel

Advanced filter is more difficult to use, but will filter the list to a separate range on the same sheet. https://www.contextures.com/xladvfilter01.html

Will something like that work for you, or are you required to figure out a combination of lookup functions for this?

[George Drivas]

Thanks for the input. Honestly I have no required methods. The intent is to search one table which is reserved for input data only to find a match. There are two columns specifically within the search table that a match is being checked for. If either column returns true, I want to output 6 columns of data from the search table across a single row that had the match return true. I want the output of that search to be in a different location so to not corrupt the data input table. Also, this report will be run a multitude of times with different input criteria for the search. So it’s not static. What I’m trying to avoid is doing a row by row match with outputs over 150 rows and have gaps from resulted matching rows. If there is a way to do a fixed output table with a row by row match that can then auto hide/show rows with all blank cells each time the report is run; that would work as well. Of special note is that this is running on an iOS version of excel for iPads. Don’t shoot the messenger. Lol. Which is the reason I wasn’t able to attach the workbook. But the above description is accurate.

Search table 21 columns and 150 rows.
Trying to match one number in a row in two possible columns
If true in either column, output 6 columns worth of data from respective row
Do this for each 150 rows
Output the data into a new table with only those 6 columns and as many rows as found in the search table array.

[MrShorty]

That still sounds like a filter to me. Look over the tutorials and see how they work, then see if you can apply something like it to your file.

[George Drivas]

Thanks for the tip. I’ll look through there and see if I find and answer or have a better question at least.