+ Reply to Thread
Results 1 to 6 of 6

Match mutliple values and then display text if found

  1. #1
    Forum Contributor
    Join Date
    09-13-2005
    Posts
    114

    Match mutliple values and then display text if found

    I've got two worksheets with values in columns A through E in both. Each column is a different criteria (i.e. column A = Name, B = Color, etc..). What I'm trying to do is create a formula to put in column F of one of the sheets that will look for an exact match of all 5 columns that are in the same row. If it finds a match it will display nothing but if it doesn't find a match it will then display an X. So for example, if in row 25 of worksheet A it had values of:

    Column A = John, Column B = Red, Column C = 3, Column D = 1, Column E = 07F

    If there is an exact match in any row of worksheet B then the formula will display nothing. If there is no exact match in any row of worksheet B then the formula would display an X in the cell. Thanks in advance for any help.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966
    Try adding an extra column to each sheet which contains the concatenated values of columns A to E.

    e.g.

    Please Login or Register  to view this content.
    Now you just have to compare one column in each sheet and you could use something like a nested IF and COUNTIF function to display your values.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try something like:

    Please Login or Register  to view this content.
    Where Sheet1!A1:F9 is the table to lookup against..

    adjust ranges to suit.

    This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Contributor
    Join Date
    09-13-2005
    Posts
    114
    I'm not sure how I could use an IF function since the I need a formula that will search every row for a match

  5. #5
    Forum Contributor
    Join Date
    09-13-2005
    Posts
    114
    As always, thanks for your help!

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Here's another way, doesn't require CTRL+SHIFT+ENTER.....

    =IF(SUMPRODUCT((Sheet1!B$1:B$9=B2)*(Sheet1!C$1:C$9=C2)*(Sheet1!D$1:D$9=D2)*(Sheet1!E$1:E$9=E2)* (Sheet1!F$1:F$9=F2)),"","X")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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