Results 1 to 6 of 6

Thread: VLOOKUP/IF (XP)

  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    VLOOKUP/IF (XP)

    The macro I requested help for works fine.
    Forgot one important feature.
    CellA28 now has a drop down called either Product1, Product2 or Product3.
    CellB28 has a list with products.
    Somewhere else in the spreadsheet cell H28 used to be a VLOOKUP that said =VLOOKUP(B28,Products,4,FALSE)
    Worked great, but now the range of products is far bigger and it has to be either column 4,9 or 14.

    I've tried =IF(A28="Product1",VLOOKUP(B28,Products,4,FALSE=IF (A28="Product2",VLOOKUP(B28,Products,9,FALSE=IF(A2 8="Product3",VLOOKUP(B28,Products,14,FALSE)))))) It doesn't give a value but it says FALSE. So I must be pretty close

    The products are on a sheet called Products, very original.

    My brains stopped working, summer has come with a bang and its very hot...

    Thanks for all the help

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLOOKUP/IF (XP)

    Try this:
    =IF(A28="Product1",VLOOKUP(B28,Products,4,FALSE),I F(A28="Product2",VLOOKUP(B28,Products,9,FALSE),IF( A28="Product3",VLOOKUP(B28,Products,14,FALSE),"No Match")))

    You cannot have = signs within your nested functions (except when it is acting as a comparison operator!) To correctly nest, you use a function as an argument of another function and you seperate them with comma's.
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP/IF (XP)

    Rudi, its almost working. It works ok with Product1, but Products2 is in column 6 nd Product3 is in column 11.
    The whole range is called Products

    Hope this make sense
    Thanks

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLOOKUP/IF (XP)

    Try this:
    =IF(A28="Product1",VLOOKUP(B28,Products,4,FALSE),I F(A28="Product2",VLOOKUP(B28,Products,6,FALSE),IF( A28="Product3",VLOOKUP(B28,Products,11,FALSE),"No Match")))

    The 3rd argument of VLookup is the column it must collect the matching value from. Change the column index number to 6 and 11 for the Products 2 and 3. See the corrected Function above!
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLOOKUP/IF (XP)

    Does this do what you want?

    =OFFSET(INDEX(INDIRECT(A28),MATCH(B28,INDIRECT(A28 ),0)),0,3)

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP/IF (XP)

    It works
    Thanks
    Another brilliant man
    A merry Christmas to you

Posting Permissions

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