Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    440
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Is this a Lookup Function

    My actual spread sheet contains over 3000 cost centre codes in one worksheet. I need to use this worksheet to match the cost centre codes and populate my "Extract" worksheet with the names of these Cost Centres in Column D. Would this be a Lookup Function?

    I have attached a small sample to demonstrate my issue.


    Kerry
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    Yes a Vertical Lookup will work. In 'EXTRACT '!D2:
    =VLOOKUP(C2,'Cost Centres'!$A$2:$B$9,2,0)

    Copy down the column

    Steve

  4. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    440
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hello Steve

    Thank you for your reply. I cannot seem to make this work.
    In my live version I have 766 possible cost centres that have to be searched through to match about 3000 rows in the Extract Worksheet. A cost Centre may be repeated in the Extract Sheet.
    Attached Images Attached Images

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    An NA error indicates that the cost centre is not found in the list. it seems from your pic that the item in C2 is text and I suspect that in Col A of the Cost Centres that they may be numbers. It is important that the both match since the text "705014" is not the same as the number 705014.

    You can Try:
    =VLOOKUP(VALUE(C2),'Cost Centres'!$A$2:$B$9,2,0)

    which will convert the value in C2 to a number. Or you can ensure that all are text or all are numbers.
    Steve

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    kerryg (2014-05-19)

  7. #5
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,472
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi Kerry

    First of all, when using lookups or match, you must compare apples with apples, i.e. if your cost center value is 'text' in your extract, then it must match a text value in your source. Ditto, if it is a 'numeric value', then it must be 'numeric' in both source and destination.

    If you are going to retrieve more than one value from the source lookup table, based on say, the cost centre code, then it is more efficient to use MATCH and INDEX to retrieve the values.

    See attached file for example.

    zeddy
    Attached Files Attached Files

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

    kerryg (2014-05-19)

  9. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,472
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi Steve

    ..you were quicker than me, again.

    zeddy

  10. #7
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    440
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Lookup and copy over data

    Thanks Steve and Zeddy both answers taught me something and got my brain working again. I love excel but dont get to use it often enough.

    This one is going to be a bit of a challenge.

    I want to add another complexity to my workings and are hoping you can help. I have attached a sample.
    In Column E of the "Extract" sheet I want to check colum A - with Sheet "Printers" for a match in columns A or D - where there is a match copy the contents of the matching cells of "printers" to the Extract sheet Colum E. If there are matches in both A and D then it will require two colums.

    Kerry
    Attached Files Attached Files

  11. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    I am confused, there seem to be NO examples of matching columns in Extract with Printers 1 time or 2 times. There are matches in Cost center. Could you explain a little more clearly? It would be nice to see what you want as a result if the row has NO matches, has 1 match and has 2 matches so we can work on replicating the results with a formula...

    Steve

  12. #9
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    440
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Steve I apologise for my dreadful example. I have taken a fair amount of time putting together a better example of my issue. It is tricky when you have to de-identify data and keep the file size small.

    In the attachment I want to search "PC-Monitors-Phones" for matching info to copy over to "Extract". I have included some records showing examples in the first few rows of "Extract."

    In "PC-Monitors-Phones" search columns A,B,C,D,E for a match to "Extract” column A. When there is a match copy over the information from "PC-Monitors-Phones" Columns G,H,I,J,K and L to the corresponding columns in "Extract" I,J,K,L,M,N,O. Where there is no match the cell should be left blank.

    Your help is always greatly appreciated.

    Kerry
    Attached Files Attached Files

  13. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    I am still confused. I don't see how the output arises from your example as some of those records in Extract output are not in the PC-Monitors-Phone records. DB2236 is in the recordset despite your example and PC-Monitors-Phone Cols F-L feed into I-o, not G-L. If there will be only 1 match in Cols A-E then you can create a "helper column" (can be hidden) to calculated the desired row. In P2 enter:
    Code:
    =MATCH(A2,INDEX('PC-Monitors-Phones'!A:E,0,ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!A:A,0))*1+ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!B:B,0))*2+ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!C:C,0))*3+ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!D:D,0))*4+ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!E:E,0))*5),0)
    and copy it down the column. This will give the matching row or a #N/A error if there is no match in any of the columns. Then in I2:
    =IF(ISNA($P2),"",OFFSET('PC-Monitors-Phones'!$A$1,$P2-1,COLUMN()-4))

    Copy this to I2:O2 and then copy I2:O2 down the columns.

    I get some of the results you have in the example, but not all, so you will have to check. I believe your example is incorrect, not my formulas, but walk me through the logic if I am mistaken, because if so, I am missing something fundamental.

    Steve

  14. #11
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    440
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Steve you are a marvel! I have had brain burn sorting this, but I think I have it right now.

    You were right, my example was incorrect.

    To help you get a picture of what I am trying to achieve I have been tasked with identifying all the assets in our department, which thousands of items. The sample I have given you is only a tiny part of the spreadsheet, but if I can get it working on this sample I can work it into my real data.

    I have come into it when part of the work was done and not in a way I would have organised it. I created a master spreadsheet with all assets ie EXTRACT. The PC-Monitors-Phones spreadsheet was sent out to workers to identify these assets and put in their information. Sadly not much thought was given to how the information received back was going to be matched with the EXTRACT. To do this manually would take forever.

    I have worked in your code and the example is attached. The only thing I dont understand is Column I on Extract (Ture or False). And a further thought is, how can a duplicate be identified?
    Attached Files Attached Files
    Last edited by kerryg; 2014-05-19 at 20:21.

  15. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,170
    Thanks
    14
    Thanked 321 Times in 315 Posts
    If you expand the formula bar, you can see the problem in I2:
    I2 Formula.PNG

    This is the same for all the formulas in Col I. For whatever reason, you pasted it into the cell twice and have essentially:
    =Formula=Formula

    Which since it is the same formula you are comparing it to, will give TRUE. You can edit I2, to just have the 1 formula and then copy it down the column or just copy the formulas from col J to col I to fix it.

    Steve
    PS, what do you mean by duplicate? and identifying it in what sense? When I used the term, I used it in the sense that a lookup would only be in 1 of the columns from A to E, it would not be in more than 1. If it appears in more than 1 then "found row" would be wrong since the "found column" would be wrong....
    Last edited by sdckapr; 2014-05-20 at 08:11.

Posting Permissions

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