+ Reply to Thread
Results 1 to 8 of 8

Comparing a table field with a field in another table and returning all matching values

  1. #1
    Registered User
    Join Date
    06-29-2017
    Location
    Bournemouth, England
    MS-Off Ver
    MS Office 2013
    Posts
    17

    Comparing a table field with a field in another table and returning all matching values

    Hi all

    I'm working on a spreadsheet and I'm trying to find values that match those in another table.

    The formula I've tried to do this is:

    {=IFERROR(
    INDEX(ClientPortfolioDataTable[Holding],
    SMALL(
    IF(
    ClientPortfolioDataTable[Holding]=Low[Holding],
    ROW(ClientPortfolioDataTable[Holding])-ROW('Client Portfolio Data Table'!B$2)+1
    ),
    ROWS('Client Portfolio Data Table'!B$2:'Client Portfolio Data Table'!B3)
    )
    ),"")}

    The [Holding] refers to the value I want to return, and Low[Holding] refers to the value I want to check it against. When I action this it just comes out as blank though.

    To clarify the process I'm trying to action, I'm wanting the formula to search the ClientPortfolioDataTable[Holding] column (Column B:B) and return any values that match the Low[Holding] column in the other table, to repeat the process I've used the ROW/ROWS function, hoping to find all entries.

    Does anyone know how I can fix this? Or is there a simpler way to do this e.g. with Power Query?

    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Comparing a table field with a field in another table and returning all matching value

    Array enter:

    =IFERROR(INDEX(ClientPortfolioDataTable[Holding],SMALL(IF(ClientPortfolioDataTable[Holding]=Low[Holding],ROW(ClientPortfolioDataTable[Holding])-2),ROW(A1))),"")

    and copy down.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    06-29-2017
    Location
    Bournemouth, England
    MS-Off Ver
    MS Office 2013
    Posts
    17

    Re: Comparing a table field with a field in another table and returning all matching value

    Hi there,

    Thanks for the reply!

    This still doesn't seem to want to work though. When I array enter the formula it only returns a blank cell. Do you have ideas why this might be?

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Comparing a table field with a field in another table and returning all matching value

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    06-29-2017
    Location
    Bournemouth, England
    MS-Off Ver
    MS Office 2013
    Posts
    17

    Re: Comparing a table field with a field in another table and returning all matching value

    Hello

    Please find attached an example workbook to better explain what I'm trying to achieve

    Thanks in advance
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Comparing a table field with a field in another table and returning all matching value

    Sorry - I thought you were looking for changes in the table - use this

    =IFERROR(INDEX(Table2[Holding],SMALL(IF(NOT(ISERROR(MATCH(Table2[Holding],Table1[Name],FALSE))),ROW(Table2[Holding])-1),ROW(A1))),"")

    Note that the -1 depends on where the tables within the sheet.
    Last edited by Bernie Deitrick; 06-30-2017 at 07:18 AM.

  7. #7
    Registered User
    Join Date
    06-29-2017
    Location
    Bournemouth, England
    MS-Off Ver
    MS Office 2013
    Posts
    17

    Re: Comparing a table field with a field in another table and returning all matching value

    Bernie, you've done it! Really well done. Apologies for me not making it clear to begin with.

    Thank you so much for your help, it's really helped me understand what was going wrong too.

    Thanks again

    Ralane

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Comparing a table field with a field in another table and returning all matching value

    No - you were clear. I was thinking about something else and interpreted your question incorrectly. Glad that it worked for you.

+ 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. Replies: 0
    Last Post: 08-15-2016, 02:07 PM
  2. [SOLVED] Calculated Field in Pivot Table, Not Returning Correct If Function
    By egarcia7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2016, 12:49 PM
  3. [SOLVED] A pivot table field calculated using other field values as fields?
    By chrisf78 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-07-2015, 03:08 AM
  4. Pivot Table field show values as % of another value field
    By amotto11 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-27-2015, 04:31 PM
  5. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  6. Replies: 3
    Last Post: 03-03-2012, 12:16 PM
  7. Replies: 7
    Last Post: 12-03-2011, 03:42 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