Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    How could you embed a worksheet name in a VLOOKUP formula? So the worksheet name could be located in a cell where the VLOOKUP formula is located.

    I attached a sample of the worksheet. the formula is =VLOOKUP(A9,'1'!$A$9:$E$74,4,FALSE) the worksheet name 1 is the where the lookup array is in this column. the next column is worksheet 2 and etc. I want the formula flexible so it can bring in the worksheet name in the cells E7 through G7. Is this possible.

    Thanks

    T
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Change the formula in E9 to

    =VLOOKUP($A9,INDIRECT("'"&E$7&"'!$A$9:$E$74"),4,FA LSE)

    This can be filled down to E19, then right to column N.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I now have an unforseen problem that I am getting #N/A errors. I tried an "IF" statement to change the #N/A errors to spaces or zeros but I am not having luck with the formula I developed. Any suggestions I am attaching the workbook.

    Thank you.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this version in E9, then fill down and right:

    =IF(ISNA(VLOOKUP($C9,INDIRECT("'"&E$7&"'!$A$9:$E$7 4"),4,FALSE)),"",VLOOKUP($C9,INDIRECT("'"&E$7&"'!$ A$9:$E$74"),4,FALSE))

    Note: you had entered the formulas as array formulas, but that is not necessary. These are "ordinary" formulas.

Posting Permissions

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