Forum Discussion

darronsmom's avatar
darronsmom
Copper Contributor
Sep 17, 2018
Solved

How to return multiple results based on multiple criteria

Hi all, I have a spreadsheet with multiple lookups, and I'm not exactly sure how to make it work. I've looked in several places, but don't see anything that matches what I am trying to do (or it's not clicking, anyway). I need to find out all of the item that meet 2 criteria. For example, if a product type in column A is "Temporary", then I need to return all of the products in column B that are "temporary" (basic lookup), and also all of the part numbers in product C that also contain the word "Temporary". So I would eventually use power query or a pivot table to group all the temporary products, and all of their applicable part numbers, all of the permanent products, etc. It's the if text contains lookup that I'm not getting - if it I was looking for a complete cell, I would be fine, I just don't know how to select based on part of the cell text. It's probably something simple that I'm just not connecting. Thanks
excel
Formulas and Functions

2 Replies

Sort By
  • erol sinan zorlu's avatar
    erol sinan zorlu
    Iron Contributor
    Oct 24, 2018
    if you want to select part of a text there are several functions
    1. SEARCH function to find if the string in a cell contains a specific text
    2. MID to extract the desired string out of a text within cell

    if you want to filter a table according to whether they include a specific text you can use "Filter" and then press the filter icon on the desired row and select "Text Filters" and select the option "Contains..." to filter out the cells which has the keyword you want.

    If you want to search entries that includes some specific text value with formula you can use and INDEX formula to sort out which cells contain the required data and add some more criteria and use MATCH-INDEX to locate the desired cell.

Resources