+ Reply to Thread
Results 1 to 23 of 23

Matching 2 tabs and return Yes/No based on info in a horizontal list

  1. #1
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Question Matching 2 tabs and return Yes/No based on info in a horizontal list

    Hi,

    I have 2 tabs.

    On Tab #2, I need to verify if the information is on Tab #1 and return a Yes/No answer depending if the cells are empty or not.

    Thank you in advance for your help!
    Attached Files Attached Files

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Try to put into D5 on ListNames tab:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and drag it down
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    It doesn't work for everyone on the tabs

  4. #4
    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
    79,368

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Tell us more - specific detail of "it doesn't work"?
    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.

  5. #5
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Applying the formula, it says that Steve doesn't live in Canada but yet he does (tab 1) and also, on tab 2, there's a name; Patrick, which isn't on the list on tab 1, can it show NA if the name doesn't appear on tab 1?

    Thanks! (sorry, trying to express myself as best as I can)

  6. #6
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Misunderstanding. I've thought that only if date is in that means he lives in Canada.
    If YES have to be for each from list on RAW please put into D5:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and drag it down

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Hi All,

    another approach

    =IF(SUMPRODUCT(('RAW Data'!A$5:A$10=C5)*'RAW Data'!B$5:N$10),"Yes","No")


    Hi Kokosek, did not mean to overlap
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  8. #8
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    new file updated if it helps!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Thanks, this seems to work!

    If I have a name on Tab 2 that isn't on Tab 1, how can I have the cell reflect NA instead of Yes/No ?

  10. #10
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You put as comments on Steve's cell that he's not exist but he's exists.
    Only Joe and Patrick do not exist.
    Last edited by KOKOSEK; 04-25-2019 at 11:50 AM.

  11. #11
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    it works except it doesn't return the "No" if someone does not live in Canada

  12. #12
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    The 3 scenarios I need the formula to validate is:

    I found the name...AND he lives in Canada ---------> Yes
    I found the name...AND he doesn't live in Canada ------> No
    I did not find the name --------> NA

    Thank you once again!
    Last edited by exced; 04-25-2019 at 12:29 PM.

  13. #13
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Sorry, I think that I don't understand you definition of living in Canada.
    I'll ask once more: is date in any of column B:N means that person lives in Canada?
    Check attached file.
    Attached Files Attached Files
    Last edited by KOKOSEK; 04-25-2019 at 01:38 PM.

  14. #14
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Yes, the date in any of column B:N means that the person lives in Canada

  15. #15
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Quote Originally Posted by exced View Post
    Yes, the date in any of column B:N means that the person lives in Canada
    So formula from my 1st post was 99% correct. 100% is on attached file in prev. post.

  16. #16
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    When I try to apply the formula in a different excel book, it doesn't work....the Yes and N/A works, but not the "No".

    I have people who do not show on my list yet they all appear with a Yes next to their name, why is that ?

    If I use this formula outside Book1, must I change anything in it if the tabs are called the same ?

  17. #17
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    In the case where I am using this formula, if the value is found in column A, I need it to validate of there's any date on same row between M and AO

    ex;

    If John is in A7, it must validate if there's any entries between M7 and AO7

    Sorry, I thought once I had the formula I could edit that aspect but I am not an expert with offset


    =IFERROR(IF(SUM(OFFSET(Insurance!$A$4,MATCH(BDP!$C19,Insurance!$A$5:$A$4000,0),0,1,15))>0,"YES","NO"),"N/A")

  18. #18
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    As I understand BDP!C19 is particular cell with name to check (original List of Names tab) and Insurance!A:A is col. with names to check (original RAW data tab).

    If you want to check include col. AO7 to check, you have to change highlighted parameter:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Column AO if I am not mistaken will be 41 (put 42 just in case )

  19. #19
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Thanks, how can I set it to start verifying from column M if there's a date?

    Right now, with the OFFSET, I believe if it finds BDP!C19 in Insurance!A:A, it will start checking for a date 1 column away, correct?

    I would like, once it matches BDP!C19 in Insurance!A:A, to start checking from column M until AO on the same row. What must I change for this to happen ?

    Whewn I test I have someone with no value from M to AO and the formula keeps returning a "Yes" when it should be a "No".

  20. #20
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    I uploaded a copy of my original file but filtered some information.

    If you look at tab BDP,

    Column AZ are accurate results I verified manually.

    When I put the formula in column BB, notice I have a bunch of discrepancies.

    Formula on tab BDP, column AZ must take ID from tab BDP, column C and verify if present on tab Insurance A:A and if it finds it, verify between M and AO if there's any data.

    Wanted outcome on tab BDP, column BB with formula:

    If the ID on tab BDP, column C is found on tab insurance A:A AND there's information between column M to AO ---> Tab BDP, column BB should display Yes
    If the ID on tab BDP, column C is found on tab insurance A:A but there's NO information between column M to AO ---> Tab BDP, column BB should display No
    If the ID on tab BDP, column C is NOT FOUND on tab insurance A:A ---> Tab BDP, column BB should display N/A.

    I hope this helps!
    Attached Files Attached Files
    Last edited by exced; 04-26-2019 at 09:18 AM.

  21. #21
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    Try then:


    Formula: copy to clipboard
    Please Login or Register  to view this content.

    accept of course with CSE

    Attached file shows that all rows where you put expected results are matching.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    07-28-2008
    Location
    montreal
    Posts
    59

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    A big thank you for your relentless efforts, it works !!!

  23. #23
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Matching 2 tabs and return Yes/No based on info in a horizontal list

    You welcome.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

+ 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] Return Value in Cell by matching Horizontal and vertical names
    By emsiti in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2016, 08:16 AM
  2. [SOLVED] Linking info from Tabs into LOOKUP using List
    By tomdss in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-15-2016, 10:26 AM
  3. Replies: 0
    Last Post: 10-07-2013, 10:24 AM
  4. Replies: 4
    Last Post: 10-02-2013, 03:00 PM
  5. How to list matching value for a horizontal (row) search
    By mobie86 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2013, 05:53 AM
  6. Replies: 1
    Last Post: 04-21-2013, 08:36 AM
  7. Search...Return...give info based on the return address
    By deek1004 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-11-2012, 03:36 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