+ Reply to Thread
Results 1 to 5 of 5

Something like COUNTIF, but it shows an actual lookup value?

  1. #1
    Registered User
    Join Date
    06-11-2008
    Posts
    20

    Something like COUNTIF, but it shows an actual lookup value?

    This might be too complex to do in an Excel formula, but I'd like to try.

    Imagine I have this table:

    A 1
    A 2 1
    A 3 1,2
    B 1
    A 4 1,2,3
    B 2 1
    B 3 1,2
    C 1
    A 5 1,2,3,4
    C 2 1

    I want to write a formula that calculates column C in that table. Column C basically: grabs the value of Column A in its own row, looks for all the times that same value has been in Column A so far, and then lists the contents of Column B for every Column with the same value in Column A.

    So, for row 3, it finds all the instances where Column A is "A", looks up the values of Column B (1, 2), and lists them.

    Is that possible? Right now I've got it simply counting the appearances so far, but listing them has eluded me.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Something like COUNTIF, but it shows an actual lookup value?

    What version of Excel do you have?

    If using Office 365 subscription, you can use TextJoin().

    If not, then I think you may need User Defined Function (UDF) in VBA, or if you have access to it, PowerQuery (Get & Transform).
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    06-11-2008
    Posts
    20

    Re: Something like COUNTIF, but it shows an actual lookup value?

    I do! What function in Excel would return multiple cells to be concatenated?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Something like COUNTIF, but it shows an actual lookup value?

    Do you mean TextJoin?

    In your case, you could do something like...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Confirmed as Array (CTRL + SHIFT + ENTER).

    See attached sample.

    NOTE: I'd recommend that you update your profile to include "MS-Off Ver" info
    Attached Files Attached Files

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    Re: Something like COUNTIF, but it shows an actual lookup value?

    If interested in something without VBA or TEXTJOIN.

    In C2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    1
    A
    1
    2
    A
    2
    1
    3
    A
    3
    1,2
    4
    B
    1
    5
    A
    4
    1,2,3
    6
    B
    2
    1
    7
    B
    3
    1,2
    8
    C
    1
    9
    A
    5
    1,2,3,4
    10
    C
    2
    1
    Dave

+ 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. [SOLVED] Issue with adding an actual value to a countif TODAY formula
    By yourik in forum Excel General
    Replies: 10
    Last Post: 09-02-2017, 11:38 PM
  2. [SOLVED] Countif formula shows value according to cell
    By andy_tap in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 09-01-2016, 11:32 AM
  3. [SOLVED] Countif only if shows a date
    By missd in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-05-2013, 09:41 AM
  4. Replies: 16
    Last Post: 07-02-2013, 02:38 AM
  5. A countif formula to pull actual value
    By Climaxgp in forum Excel General
    Replies: 7
    Last Post: 04-09-2011, 06:56 AM
  6. Drop Down List in form shows numeric value in database-want to show actual value
    By DonutMonster in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2009, 09:32 AM
  7. COUNTIF formula which shows a %
    By dandavis1 in forum Excel General
    Replies: 2
    Last Post: 03-05-2009, 06:07 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