Results 1 to 4 of 4

Thread: VLOOKUP issues

  1. #1
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    We are having issues with the VLOOKUP function. Not too sure what is incorrect in the formula after numerous attempts. Three files are attached as one zipped file. We would like to have the the color columns match.

    File to have working VLOOKUP function is "City Projects Master Catalog" and the Column to match to is B (the one shaded in Red).

    City Projects Master Catalog:
    Column B - Lookup reference (is the only one in all three files)
    Column J - Match to Column G from "Life to Date CIP"
    Column L - Match to Column H from "Life to Date CIP"
    Column O - Match to Column I from "CIP Master File"
    Column P - Match to Column J from "CIP Master File"

    Thank you very much.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='aapke' post='795396' date='28-Sep-2009 19:19']We are having issues with the VLOOKUP function. Not too sure what is incorrect in the formula after numerous attempts. Three files are attached as one zipped file. We would like to have the the color columns match.

    File to have working VLOOKUP function is "City Projects Master Catalog" and the Column to match to is B (the one shaded in Red).

    City Projects Master Catalog:
    Column B - Lookup reference (is the only one in all three files)
    Column J - Match to Column G from "Life to Date CIP"
    Column L - Match to Column H from "Life to Date CIP"
    Column O - Match to Column I from "CIP Master File"
    Column P - Match to Column J from "CIP Master File"

    Thank you very much.[/quote]
    I am uncertain of just what comparisons you are trying to make and which column you are trying to obtain a return from; but you should note the following.

    The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. For more information, see Default sort orders
    Regards
    Don

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='wdwells' post='795401' date='29-Sep-2009 02:33']The values in the first column of table_array must be placed in ascending sort order[/quote]
    That is only true if the 4th argument of VLOOKUP is TRUE or omitted. If it is FALSE, table_array does not need to be sorted.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The formula in J12 would be

    =VLOOKUP(B12,'[Life to Date CIP Expenditures.xls]Consolidation'!$C$2:$H$123,5,FALSE)

    In L12

    =VLOOKUP(B12,'[Life to Date CIP Expenditures.xls]Consolidation'!$C$2:$H$123,6,FALSE)

    In O12

    =VLOOKUP(B12,'[CIP Master File.xls]08-31-09 PM Sort'!$C$2:$J$149,7,FALSE)

    And in P12

    =VLOOKUP(B12,'[CIP Master File.xls]08-31-09 PM Sort'!$C$2:$J$149,8,FALSE)

    To suppress the #N/A error value if there is no match, you can expand the formulas, for example in J12:

    =IF(ISERROR(VLOOKUP(B12,'[Life to Date CIP Expenditures.xls]Consolidation'!$C$2:$H$123,5,FALSE)),"",VLOOKUP(B1 2,'[Life to Date CIP Expenditures.xls]Consolidation'!$C$2:$H$123,5,FALSE))

Posting Permissions

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