+ Reply to Thread
Results 1 to 11 of 11

Check if cell value exists in other table.

  1. 05-30-2019, 02:09 AM #1
    tiger99
    tiger99 is offline
    Registered User
    Join Date
    01-27-2014
    Location
    Brisbane
    MS-Off Ver
    Excel for Office 365
    Posts
    25

    Check if cell value exists in other table.

    Hi All,

    Thanking you in advance for your help. I have a column of values that I would like to check if it exists in another worksheet. Sounds simple right? As an example, in the attached file in the "NEW FBCs worksheet" I am trying to check if cell c7 (KQFLEX) for ABXSYD (b7) exists in the Known FBC's work sheet. Tables are in different formats. I've tried index/matches, pivots etc, but can't figure this one out.

    Thanks again,

    Pete
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-30-2019, 02:24 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Check if cell value exists in other table.

    Please add a few rows of manually calculated results data and explain how you have reached the results. I have no idea what the answer should be for the codes on row 7!!!
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 05-30-2019, 02:31 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Check if cell value exists in other table.

    Just a guess - is this what you mean? In E2 copied down:

    =IFERROR(IF(INDEX('Known FBCs'!$F$2:$S$118,MATCH('NEW FBCs'!B2,'Known FBCs'!$B$2:$B$118,0),MATCH('NEW FBCs'!D2,'Known FBCs'!$F$1:$S$1,0))=C2,"Known",""),"")
    Reply With QuoteRegister To Reply
  4. 05-30-2019, 03:25 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Check if cell value exists in other table.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  5. 05-30-2019, 03:34 AM #5
    tiger99
    tiger99 is offline
    Registered User
    Join Date
    01-27-2014
    Location
    Brisbane
    MS-Off Ver
    Excel for Office 365
    Posts
    25

    Re: Check if cell value exists in other table.

    Thanks. Yes that’s what I mean. I was just using row 7 as an example. Works perfectly. Thanks for your quick help.

    Pete
    Reply With QuoteRegister To Reply
  6. 05-30-2019, 03:36 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Check if cell value exists in other table.

    No problem. Please mark the thread as solved.
    Reply With QuoteRegister To Reply
  7. 05-30-2019, 07:09 PM #7
    tiger99
    tiger99 is offline
    Registered User
    Join Date
    01-27-2014
    Location
    Brisbane
    MS-Off Ver
    Excel for Office 365
    Posts
    25

    Re: Check if cell value exists in other table.

    Hi again AliGW. I've just performed a spot check and identified an issue. If you're able to have another look that would be much appreciated. I've slightly amended the coding as : =IFERROR(IF(INDEX('Known FBCs'!$F$2:$S$118,MATCH('NEW FBCs'!B3,'Known FBCs'!$B$2:$B$118,0),MATCH('NEW FBCs'!D3,'Known FBCs'!$F$1:$S$1,0))=C3,"Known","New1"),"New2")

    New2 represents error outcome and results are all correct
    New1 represents newly found FBC and is incorrect for those Markets that have more than one row in the Known FBCs worksheet. Example: ADLBNE correctly identifies SDEAL as "Known", but incorrectly identifies SDEALD as "New1". It would appear it matches the first instance of ADLBNE S (RBD) and does not continue further. I've attached the edited version of the book.

    Thanks,

    Pete
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 05-31-2019, 01:19 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Check if cell value exists in other table.

    INDEX MATCH is designed to identify the first instance only. I'll have a look and see if there's a way round it.
    Last edited by AliGW; 05-31-2019 at 02:08 AM.
    Reply With QuoteRegister To Reply
  9. 05-31-2019, 01:32 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Check if cell value exists in other table.

    Please try this alternative:

    =IFERROR(IF(SUMPRODUCT(('Known FBCs'!$B$2:$B$118=B3)*('Known FBCs'!$F$1:$S$1=D3)*('Known FBCs'!$F$2:$S$118=C3))>0,"Known","New2"),"New1")
    Reply With QuoteRegister To Reply
  10. 05-31-2019, 02:03 AM #10
    tiger99
    tiger99 is offline
    Registered User
    Join Date
    01-27-2014
    Location
    Brisbane
    MS-Off Ver
    Excel for Office 365
    Posts
    25

    Re: Check if cell value exists in other table.

    Bingo!. You're a whiz. Works perfectly. I will investigate how this works. You're help is very much appreciated. Have a great weekend.

    Pete
    Reply With QuoteRegister To Reply
  11. 05-31-2019, 02:06 AM #11
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Check if cell value exists in other table.

    I'm sure you know that you can use the Evaluate Formula feature to do so, but essentially SUMPRODUCT used this way creates a matrix of TRUE/FALSE to identify the intersection point of the three criteria and return a count of the number of times that is found.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Check if a value exists in a table
    By Kappany in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-08-2015, 02:42 AM
  2. Check a value from another table exists in cell and return this value.
    By jram in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-06-2015, 02:28 AM
  3. VBA Code to check If value exists in a table
    By ysrikhanta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2012, 05:09 AM
  4. IF assistance to check if value exists in a table first
    By penfold in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2010, 11:46 AM
  5. Check if a filter exists on a pivot table
    By spinkung in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2010, 12:03 PM
  6. How can I use VBA ADO to check if a table exists in an Access DB?
    By Ai_Jun_Zhang in forum Excel General
    Replies: 3
    Last Post: 08-24-2005, 04:05 PM
  7. How can I use VBA ADO to check if a table exists in an Access DB?
    By Ai_Jun_Zhang in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2005, 02:52 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1