+ Reply to Thread
Results 1 to 6 of 6

How to find value of a cell based on multiple criteria to look into multiple rows and cols

  1. #1
    Registered User
    Join Date
    03-20-2013
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    4

    How to find value of a cell based on multiple criteria to look into multiple rows and cols

    I am having issues with finding a specific value based on multiple criteria and the criteria needs to be searched into multiple rows and columns. I have tried using match, index, if, row functions but nothing worked. I have included an attachment with an example and also posed the question. Hope that helps. If you look at the example, I am basically trying to find the value of the goods that matches the company name, and also matches the states in row 1 and Col B. I know how to use the match function to find the row number and col number for the criteria but i have multiple cols that needs to be looked into for the criteria and that is where I am failing. Please help.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-20-2019
    Location
    costa rica
    MS-Off Ver
    windows 10
    Posts
    10

    Re: How to find value of a cell based on multiple criteria to look into multiple rows and

    I opened the file, but I am not sure I understand the values on the table. For what I can see, Row 4 has the values that were shipped from CA, and Column E has the values that were shipped to CA? So that Cell E4, shows the values shipped from CA to CA? Is this correct?

  3. #3
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How to find value of a cell based on multiple criteria to look into multiple rows and

    If you're looking for only the value for a specific company shipped to and from CA, try this array formula, entered with ctrl+shift+enter:
    Please Login or Register  to view this content.
    This will retrieve a single value at the intersection of the CA row and CA column, for Test Company 1.

    If you're looking to sum up every value that shipped from CA plus every value that was shipped to CA, try this formula:
    Please Login or Register  to view this content.
    This will add up all the values in the CA row for Test Company 1, plus all the values in the CA column for Test Company 1. The second formula will not include the cell where the CA row and CA column intersect.

    Does either of those give you the result you're looking for?
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  4. #4
    Registered User
    Join Date
    03-20-2013
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to find value of a cell based on multiple criteria to look into multiple rows and

    Yes you are correct. Also, the formula needs to match the company names as well. For e.g. I am looking at Test Company 1 in this example.

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to find value of a cell based on multiple criteria to look into multiple rows and

    You can try the aggregate function to get the first, the second, ...

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    B17 is the company, B18 is the State (column B) and B19 is the State in row 1.

  6. #6
    Registered User
    Join Date
    03-20-2013
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to find value of a cell based on multiple criteria to look into multiple rows and

    Thank you so much. The former one worked perfectly.

+ 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. Find non-blank text cell based on multiple criteria
    By Villalobos in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-29-2018, 05:16 AM
  2. Find and Delete rows based on criteria in multiple columns
    By Doctor_H in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2013, 08:03 PM
  3. Find cell and paste based on multiple (two) criteria
    By conlinuk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2012, 12:50 PM
  4. Find cell and paste based on multiple (two) criteria
    By conlinuk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-28-2012, 12:25 PM
  5. [SOLVED] Remove multiple rows based on multiple cell's criteria
    By wyanrandy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2012, 04:28 PM
  6. Find rows and delete them based on multiple criteria
    By kostas in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-31-2012, 11:59 AM
  7. Replies: 1
    Last Post: 05-18-2009, 11:21 AM

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