+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    08-07-2018
    Location
    Charlotte, NC
    MS-Off Ver
    Excel for IOS
    Posts
    3

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

    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.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,862

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

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    08-07-2018
    Location
    Charlotte, NC
    MS-Off Ver
    Excel for IOS
    Posts
    3

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

    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.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,862

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

    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.

  5. #5
    Registered User
    Join Date
    08-07-2018
    Location
    Charlotte, NC
    MS-Off Ver
    Excel for IOS
    Posts
    3

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Complex index match lookup
    By vitt4300 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2017, 03:37 PM
  2. [SOLVED] Complex LOOKUP and Return Criteria possibly using INDEX/MATCH...
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 04-19-2017, 08:43 AM
  3. [SOLVED] How to use Index and Match in a complex table
    By wedzmer in forum Excel General
    Replies: 8
    Last Post: 03-04-2016, 02:50 PM
  4. Lookup using Index & Match to Return Unique Output
    By apdiego in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2013, 03:45 AM
  5. Troubles with Complex Lookup/INDEX function.... Way over my Head
    By gamerofthebeer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-01-2013, 10:26 AM
  6. [SOLVED] Complex index/match lookup
    By rinkjames in forum Excel General
    Replies: 4
    Last Post: 06-10-2012, 01:27 PM
  7. Replies: 1
    Last Post: 08-17-2011, 06:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1