+ Reply to Thread
Results 1 to 4 of 4

Help with =if and =vlookup

  1. #1
    Registered User
    Join Date
    04-04-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Help with =if and =vlookup

    I have everything set up correctly for the function, but one of the requirements is

    . If the position entered is not a valid position or is misspelled, the word Invalid should be displayed in the cell.

    The function is

    =IF(G15=$C$5,0,VLOOKUP(D15,table1,2,TRUE))

    What would I add to make this correct?

    Thank you!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with =if and =vlookup

    Hi,

    What do you mean by 'valid position'? Is this a cell reference, a cell value or what.
    Then assuming something is misspelled how is any formula expected to be able to detect this fact. Is there a list of valid spellings somewhere?

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

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

    Re: Help with =if and =vlookup

    Are you looking for exact matches then?

    Maybe:

    =IF(G15=$C$5,0,IFERROR(VLOOKUP(D15,table1,2,FALSE),"invalid"))

    the TRUE in VLOOKUP will always return something....as long as there is a table of values
    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
    Registered User
    Join Date
    04-04-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Help with =if and =vlookup

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    What do you mean by 'valid position'? Is this a cell reference, a cell value or what.
    Then assuming something is misspelled how is any formula expected to be able to detect this fact. Is there a list of valid spellings somewhere?

    Regards
    It means that if when all of the data was entered, the column of the type of worker they are, if it is incorrect, it should catch it and then it should say invalid.

    Hope that was somewhat clear

+ 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