Results 1 to 5 of 5

Thread: Assistance with an IF, OR, VLookup formula

  1. #1

    Assistance with an IF, OR, VLookup formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I have an issue I can't seem to figure out

    Question: How do I combine multiple VLookups in 3 datasets into into one formula. I will like to be able to select an option in a given drop down list, it will compare different worksheets and populate data relevant to what has been selected:

    Individually the below formulas work and pull the relevant data from the relevant cell, but I need to be able to put them all in one cell (one formula).

    Group_1
    =IF(ISERROR(VLOOKUP(L10,StandardCONTROL,11,FALSE)),"",VLOOKUP(L10,StandardCONTROL,11,FALSE))
    =IF(ISERROR(VLOOKUP(L10,LocalCONTORLREF,11,FALSE)),"",VLOOKUP(L10,LocalCONTORLREF,11,FALSE))
    =IF(ISERROR(VLOOKUP(L10,Generic,11,FALSE)),"",VLOOKUP(L10,Generic,11,FALSE))


    Group_2
    =IF($M10="Key",VLOOKUP($L10,StandardCONTROLREF,5,FALSE),0)
    =IF($M10="Key",VLOOKUP($L10,LocalCONTORLREF,5,FALSE),0)
    =IF($M10="Key",VLOOKUP($L10,Generic,5,FALSE),0)


    Worksheet related information

    My issue is focused in the R2R worksheet What I am trying to achieve is as below:
    When I select an option from column L10 onwards dropdown list:
    This will update the related Key control(Column M) from the necessary database
    This will also update columns E-J

    However I am able to only get this to work presently if the formula is to only one specific dataset/database not all 3

    I will like to be able to select the data from the dropdown list in Column L (row 10 onwards) which will then populate then populate the respective columns E-J and M based on the appropriate database.

    Data in the dropdown list in column L are outlined in the datasets
    StandardControlRef - Standaards database
    LocalControlRef - Local Databse
    Generic - Generic Database


    Please let me know if you need any additional information.

    Thank you
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    For group_1 perhaps:
    in cell M10:
    =IF(ISERROR(VLOOKUP(L10,GenericCONTROLREF,11,FALSE)),IF(ISERROR(VLOOKUP(L10,LocalCONTROLREF,11,FALSE)),IF(ISERROR(VLOOKUP(L10,StandardCONTROLREF,11,FALSE)),"not found",VLOOKUP(L10,StandardCONTROLREF,11,FALSE)),VLOOKUP(L10,LocalCONTROLREF,11,FALSE)),VLOOKUP(L10,GenericCONTROLREF,11,FALSE))

    that was one line. I notice you're using IF(ISERROR(…, but I think you have available to you IFERROR(… which will shorten the formula:
    =IFERROR(VLOOKUP(L10,GenericCONTROLREF,11,FALSE),IFERROR(VLOOKUP(L10,LocalCONTROLREF,11,FALSE),IFERROR(VLOOKUP(L10,StandardCONTROLREF,11,FALSE),"not found")))

    For the other group, in cell E10:
    =IF($M10="Key",IFERROR(VLOOKUP($L10,GenericCONTROLREF,5,FALSE),IFERROR(VLOOKUP($L10,LocalCONTROLREF,5,FALSE),IFERROR(VLOOKUP($L10,StandardCONTROLREF,5,FALSE),"not found"))),0)

    in cell F10:
    =IF($M10="Key",IFERROR(VLOOKUP($L10,GenericCONTROLREF,6,FALSE),IFERROR(VLOOKUP($L10,LocalCONTROLREF,6,FALSE),IFERROR(VLOOKUP($L10,StandardCONTROLREF,6,FALSE),"not found"))),0)

    etc.

    In the formulae above, you may want to tweak which order the databases are searched in.
    Taking:
    =IF($M10="Key",IFERROR(VLOOKUP($L10,GenericCONTROLREF,6,FALSE),IFERROR(VLOOKUP($L10,LocalCONTROLREF,6,FALSE),IFERROR(VLOOKUP($L10,StandardCONTROLREF,6,FALSE),"not found"))),0)

    the order of search is Generic, Local then Standard.
    Last edited by p45cal; 2015-08-16 at 10:50 PM.

  3. #3
    Thank you, thank you, this has taken up quite sometime and I can see my mistakes. Thank you p45cal.

    One more question please, if I have an additional word besides "Key", i.e. "Supporting", how can I get the formula to check for either word and return the corresponding values depending on which word is returned?

    =IF($M10="Key",IFERROR(VLOOKUP($L10,GenericCONTROLREF,5,FALSE),IFERROR(VLOOKUP($L10,LocalCONTROLREF,5,FALSE),IFERROR(VLOOKUP($L10,StandardCONTROLREF,5,FALSE),"not found"))),0)

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    =IF(OR($M10="Key", $M10="Supporting"),IFERROR(VLOOKUP($L10,GenericCONTROLREF,5,FALSE),IFERROR(VLOOKUP($L10,LocalCONTROLREF,5,FALSE),IFERROR(VLOOKUP($L10,StandardCONTROLREF,5,FALSE),"not found"))),0)

    (untested)

  5. #5
    Thank you p45cal, this worked excellently, I am most grateful

Posting Permissions

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