+ Reply to Thread
Results 1 to 13 of 13

Complex. Function to return value of data in a comlumns based on two cells of information

Hybrid View

  1. #1
    Registered User
    Join Date
    07-27-2015
    Location
    Gold Coast, Australia
    MS-Off Ver
    13
    Posts
    9

    Re: Complex. Function to return value of data in a comlumns based on two cells of informat

    Sorry I might not have been clear enough.
    The data is not meant to be symmetrical, it could come up in any order, all "dogs"except for one "no".

    What I want to retrieve is a value if all the data to the right of C up to L is of the same content as cells B and C for the corresponding row.

    Ie. row 3: cells D:L contain only dog and no so Id like it to return a value of "x"
    Whilst row 4 cells D:L contain cat no and dog so I'd like it to return a value of "y"

    IF row 4 cells D:L contained only cat and no then I'd to have a value of "x" returned.

    Is that clearer?

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2505
    Posts
    13,766

    Re: Complex. Function to return value of data in a comlumns based on two cells of informat

    Yes. I had another look and determined that was the case. I didn't withdraw my post in time. Apologies.

    Try this in M3 and fill down.

    =IF(SUMPRODUCT(($B3=$D3:$L3)+($C3=$D3:$L3))=COUNTA($D3:$L3),"x","y")

    Formula: copy to clipboard
    =IF(SUMPRODUCT(($B3=$D3:$L3)+($C3=$D3:$L3))=COUNTA($D3:$L3),"x","y")


    Oh BTW "Dog" in D3 had a trailing space. It has to be deleted or this formula won't work.
    Last edited by FlameRetired; 07-28-2015 at 01:07 AM.
    Dave

  3. #3
    Registered User
    Join Date
    07-27-2015
    Location
    Gold Coast, Australia
    MS-Off Ver
    13
    Posts
    9

    Re: Complex. Function to return value of data in a comlumns based on two cells of informat

    result of formula.png

    Unfortunately that didn't work.

    Thank you though, do you have any other suggestions?

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2505
    Posts
    13,766

    Re: Complex. Function to return value of data in a comlumns based on two cells of informat

    Yes. Try this modified version.

    =IF((SUMPRODUCT(--EXACT(B3,D3:L3))>0)*(SUMPRODUCT(--EXACT(C3,D3:L3))>0)*SUMPRODUCT(EXACT(B3,D3:L3)+EXACT(C3,D3:L3))=COUNTA($D3:$L3),"x","y")

    Did you read about the trailing space in my edited post #5?

  5. #5
    Registered User
    Join Date
    07-27-2015
    Location
    Gold Coast, Australia
    MS-Off Ver
    13
    Posts
    9

    Re: Complex. Function to return value of data in a comlumns based on two cells of informat

    Hi Flame retired,

    I did fix up that error.

    Your formula worked well except for when all values were either "No" or "Dog" for example.

    Which unfortunately they often will be.

    Is there anything else that will work?

    Thank you so much.

    Flame Retired new formula.png

+ 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. importing PI data based on information in a range of cells
    By frdranger25401 in forum Excel General
    Replies: 3
    Last Post: 03-22-2015, 09:20 PM
  2. Formula to pull information based on information in different cells.
    By apmi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-26-2014, 03:57 PM
  3. [SOLVED] Return a value based on different information in one cell
    By Shezza in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-23-2013, 01:21 AM
  4. [SOLVED] RANK function - to return data based on 2 different columns
    By Adsup01 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-30-2013, 08:17 PM
  5. Replies: 2
    Last Post: 04-16-2012, 09:42 AM
  6. Need help with complex IF function to return values on different sheet
    By mccormickj in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-02-2011, 08:07 AM
  7. If function to return no. based on 2 cells?
    By duffer in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-15-2008, 09:10 AM

Tags for this Thread

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