Vlookup output as a cell argument

  • I have a sub called GETCOLOR which retrieves the index color of a cell.


    If I type it directly as =GETCOLOR (AJ6,”index”) it yields the background color of cell AJ6, which is 14 (green)


    However, I need to get the background color of that same cell, but expressed as the result of a VLOOKUP function. AJ6 is the output of the function =VLOOKUP($D$5,$CV$2:$CW$314,2,FALSE) and I need to use that as the cell (first) argument for the GETCOLOR sub.


    If I use it this way =GETCOLOR(VLOOKUP($D$5,$CV$2:$CW$314,2,FALSE),“index”) I get a #VALUE! Error


    “Excel Formulas and Functions for Dummies” states that for the function =CELL(“contents”,A1), if the argument cell contains a formula, the function returns the result of the formula and not the formula itself. Since the output of the VLOOKUP is AJ6, I tried the following:


    =GETCOLOR(CELL(“contents”,(VLOOKUP($D$5,$CV$2:$CW$314,2,FALSE)),“index”), but it says I’ve entered too many arguments for this function.


    I suspect that it has something to do with the placement of parentheses, but none of the placement combinations I've tried have worked. I'm sure it's something obvious and simple, but I can't seem to solve it - can anyone point out my error(s)?

  • Hello,


    To make everything much easier for everyone ... just attach a sample file to illustrate your expected result ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Dear Carim,


    Per you suggestion, I have attached a pared-down version of the file (most extraneous details removed). Also, here are some relevant notes (please pardon the cap's - not shouting, just copied the upper case notes from the spreadsheet, where they are also included). I greatly appreciate any assistance that you can provide.


    At cell C4, I have listed the formulas used and steps taken to build the code for Cell D7 and the results.


    MY QUESTION FOR THIS FORUM RELATES TO THE PROPER CODE TO RETRIEVE THE INDEX COLOR OF THE CELL ASSOCIATED WITH THE CONDO NUMBER ENTERED INTO CELL 'D5' ('43,' FOR EXAMPLE - WHICH IS THE BLUE CELL IN BULDING B - OR OTHER CONDO NUMBER AS QUERIED BY THE USER). ONCE IT IS WORKING, THIS CODE WILL RESIDE IN CELL 'D7.'


    ULTIMATELY (WHEN DEBUGGED) THE CODE FOR 'D7' WILL UTILIZE THE UDF "GetColor" WITH THE INDEX COLOR ASSOCIATED WITH THE CELL ADDRESS FOR THE SYMBOL REPRESENTING THE CONDO NUMBER ENTERED INTO CELL 'D5' AS THE Rng ARGUMENT AND "index" AS THE ColorFormat ARGUMENT.


    THE CELLS IN ROW 13 ARE ONLY COLORED TEMPORARILY FOR PURPOSES OF TESTING/VERIFYING CORRECT COUNTS.


    FOR PUPOSES OF A SIMPLIFIED .xlsm FILE (attached) FOR THIS POST, THE VLOOKUP TABLE (CV2:CW44) IN ONLY PARTIALLY POPULATED. THE FIRST COLUMN IS THE CONDO NUMBER, THE SECOND COLUMN IS THE CELL ADDRESS FOR THE SYMBOL REPRESENTING THAT CONDO.


    THE 'INPUT-QUERY INTERFACE' IS CURRENTLY NON-FUNCTIONAL (FUTURE IMPLEMENTATION).


    COMMENT CAPTIONS (I.E. INDIVIDUAL 1-, 2- OR 3-DIGIT CONDO UNIT NUMBERS) ARE NOT YET FUNCTIONAL (FUTURE IMPLEMENTATION).

  • Thanks for your test file :)


    For your Step 3, you could test following formula


    Code
    =CELL("CONTENTS",INDIRECT(VLOOKUP($D$5,$CV$2:$CW$44,2,FALSE)))


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • ... and for your Step 4


    you could also test following formula, using your UDF :


    Code
    =GetColor(INDIRECT(VLOOKUP($D$5,$CV$2:$CW$44,2,FALSE)),"index")


    Hope this will help

    :)

  • Once you have tested both formulas ....feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Very happy to hear you could fix your problem :)


    Thanks a lot for your Thanks ... AND for the Likes :thumbup:


    Also, wanted to congratulate you for a very smart spreadsheet ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,


    Could you please post the VBA code which needs to be fixed ....;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!