Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Matching columns on seperate sheets

    I have a heading called asset type in column A and beneath this I have the various asset types for eg Corporate signage, Furniture and Fittings etc, In col H I have a heading called Departments and below this the varios departments for eg Administration, Parts etc and in Column P I have a heading Profit Code. These are all on sheet 1

    On sheet 3, I have the Asset Type Dept Code Split

    Under asset type I will have for eg Corporate signage
    under Dept , I will have for Eg Administration
    under code I will have for eg 191475
    Under Split i will have for eg 100

    I need a code in column P on sheeet 1 that where the asset type and department on sheet 1 and 3 match, that the code and
    for Eg Sheet 1 where the asset type is say Corporate signage and the department is Administration and it matches the asset type & department on sheet 3, the under profit code in Col P , the following must appear 191475= 100

    Presently I am using a linking formula, but it is time consuming



    I have attached a sample workbook


    Your assistance in this regard wil be most appreciated
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts

    Use Vlookup function?

    The attached updated workbook does two things:

    1. Concatenates the asset type and department names to give a single lookup value
    2. Uses vlookup() to retrieve the values for the profit code from sheet 3

    Is that what you want? Use Excel help to find out more about vlookup(). You can hide the columns with the concatenated codes.

    Note that one code doesn't exist in sheet 3 so the lookup returns #NA. You can instead insert a message or default value by extending the vlookup to a formula like

    =if(isna(match(value,table,false)),"default",vlook up(value,table,column,false))

    so if there is no matching value in the lookup the cell shows "default" (or whatever you want to put in there)

    Ian.
    Attached Files Attached Files

  3. The Following User Says Thank You to iansavell For This Useful Post:

    HowardC (2012-06-28)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Ian

    Thanks for the help, much appreciated.This is exactly what I am looking for.Very clever to add the additional column in order to calculate the Vlookup. Excel is such a powerful tool. I've learnt more from these forums than from Excel books, although the books have given me a good grounding

    Regards

    Howard

Posting Permissions

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