Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    IF - ISTEXT - VLOOKUP STATEMENTs

    I'm trying to tie a "IF" Statement with a "ISTEXT" and a "VLOOKUP" and I'm not having much successs. I am attaching a sample of what I'm strying to do. Be appreciative for any help.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    It's hard to test this without the lookup file but here are a couple of things I noticed.
    Your formula,
    '=IF(ISTEXT(A7)="TRUE",VLOOKUP(A7,'FY 11 ACTUAL - FY 12 PROJ'!$A$10:$AD$472,20,FALSE),"",(VLOOKUP(A7,'FY 11 ACTUAL - FY 12 PROJ'!$A$10:$AD$472,20,FALSE)))

    Changes,
    =IF(ISTEXT(A7),VLOOKUP(A7,'FY 11 ACTUAL - FY 12 PROJ'!$A$10:$AD$47220,FALSE),VLOOKUP(A7,'FY 11 ACTUAL - FY 12 PROJ'!$A$10:$AD$472,20,FALSE))

    You don't need the ="TRUE" since ISTEXT returns true or false.
    IF only takes 3 arguments Test, True Action, False Action...thus I removed the "" which would be a 4th argument. Unless of course you wanted the False Action to return a blank then you would need a close paren after the "" and get rid of the following code.
    I also don't understand why both VLOOKUPs are identical?

    It would help if you could attach the other workbook or a like sample and explain exactly what the formula is supposed to return under what circumstances.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    See attached file.
    It seems you are trying to return the value from sheet [SOURCE] column [H] for the item matching the entry in column [A] on sheet [DB].

    So you can use the MATCH function to look for an exact match.
    Then you can use INDEX to return the value.
    If it can't find the match, the IFERROR tells it to leave a "" instead.

    zeddy
    Attached Files Attached Files

  4. The Following User Says Thank You to zeddy For This Useful Post:

    MNN (2012-05-28)

  5. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you both for the alternatives. I am getting some #N/A returned as the result. Is there a way to add an "ISERR" and just get a blank space in the result field.

    I have to look into the functionality of the "MATCH & INDEX" function. I really never used them and therefore I do not know much about them. Are they as credible and as easy as the "VLOOKUP" function. How does each one work individually and collectively?

    Thank you both again.

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    If you =IFERROR(xxxxxxx,yyyy), this says do whatever the formula xxxxxxxx says, but if it returns an error, then just put what's in yyyy instead.

    MATCH and INDEX are together more delightful to use than VLOOKUP.

    When searching a whole column, MATCH can be used to find the row number for the matching item e.g. row 27.
    (It can also return the column when searching a row).

    INDEX(K:K, row) simply returns the entry from column [K] for the given row.
    So, used together, it's just..
    =INDEX(AW:AW,MATCH(what, where, FALSE)) to return an entry from column [AW]
    (If it's on another sheet, point to the required column to have the sheet name included in the formula).
    FALSE means look for an EXACT match.

    If MATCH can't find what you are looking for, it will return #N/A
    So, use =IFERROR( ..to deal with this.

    Hope this helps

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •