+ Reply to Thread
Results 1 to 7 of 7

Help! I need to validate that a value exists in a row

  1. #1
    Registered User
    Join Date
    07-21-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    3

    Help! I need to validate that a value exists in a row

    All,

    I have scoured the internet trying to find an answer, but with no success.

    The problem: I have table of people and their preferences. I want to validate that a preference exists for that person. Vlookup and Hlookup are useless as both the rows and columns are dynamic.

    To illustrate, here is an example table:

    Column 1 Column 2 Column 3 Column 4
    Name Pref 1 Pref 2 Pref 3
    John Flowers Apples Trucks
    Cindy Cars Reading Food


    I have a list of preferences and when I put in their name, I want it to validate that they have that preference listed:

    Preference Name Exists?
    Apples John Yes
    Apples Cindy No

    I have tried using various combinations of index and match, but I can't seem to get one to work.

    Please help!

    Thanks!

    Scottymagic

  2. #2
    Forum Contributor
    Join Date
    07-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Help! I need to validate that a value exists in a row

    =IF(COUNTIF(INDEX(B:D,MATCH("John",A:A,0)),"Apple")>0,"Yes","No")

  3. #3
    Registered User
    Join Date
    07-21-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    3
    Quote Originally Posted by SlipEternal View Post
    =IF(COUNTIF(INDEX(B:D,MATCH("John",A:A,0)),"Apple")>0,"Yes","No")
    Thank you for the quick response. I'm still having issues. I get a #REF error when using INDEX. I would imagine it's because they can list up to 20 preferences, so some areas are blank. Some could list more some could list less.

    Ex: INDEX(B:D,MATCH("John",A:A,0))=#REF

    Suggestions?

    Scottymagic86

  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,005

    Re: Help! I need to validate that a value exists in a row

    Try

    =IF(COUNTIF(INDEX($B:$D,MATCH(H1,A:A,0),0),G1)>0,"Yes","No")

    G1=Preference

    H1=Name

    Data in column A:D
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help! I need to validate that a value exists in a row

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    07-21-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    3

    Re: Help! I need to validate that a value exists in a row

    FDibbins,

    Understood. I will upload dummy workbooks in the future. On another note, I got it to work!! Thank you all for putting me on the right track.

    Scottymagic

  7. #7
    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,387

    Re: Help! I need to validate that a value exists in a row

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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 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] how to validate
    By var in forum Excel General
    Replies: 1
    Last Post: 07-11-2012, 05:07 PM
  2. Validate two fields when one field you need a formula to validate
    By cmwilbur in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-29-2010, 11:32 AM
  3. validate value
    By soul_krasty in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-28-2009, 12:13 AM
  4. Validate help
    By msals22 in forum Excel General
    Replies: 6
    Last Post: 03-30-2009, 01:04 PM
  5. [SOLVED] VALIDATE WB NAME
    By AD108 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2006, 01:00 AM
  6. How to validate
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2006, 08:13 PM
  7. [SOLVED] Validate Named Range Exists
    By John Jost in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-05-2005, 04:20 PM
  8. Validate drop down box
    By DTTODGG in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-11-2005, 10:30 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