Using of VLOOKUP and ISNUMBER

    Dear Ozgrid Members,



    My file contains staff names in AB. I prepared this formula to look up staff names in column C.


    Code
    =IF(ISNUMBER(VLOOKUP(AB7,$C$8:$K$53,2,FALSE)),"Done","Undone")




    When found, then it will look at next 6 cells (C8 to K8); if any number is found (in any of those 6 cells), this will be displayed as "Done" in Column AD, or if not found any number value, then it will be "undone".


    It does not give me the desired result. What's wrong with it?



    Any help would be great. Thanks in advance.


    Onexc

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke    [/SIZE]

    Re: Using of VLOOKUP and ISNUMBER


    Attach your workbook showing where you want the formula and what result you need from that formula.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Using of VLOOKUP and ISNUMBER


    Quote from KjBox;794647

    Attach your workbook showing where you want the formula and what result you need from that formula.


    Hello KjBox,


    Thank you for your prompt reply. Uploading the file you requested with the desired results.


    Kind regards,


    Onexc

    Re: Using of VLOOKUP and ISNUMBER


    Try this in cell L4 copied down


    =IF(COUNT(C4:H4)>0,"Done","Undone")

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Using of VLOOKUP and ISNUMBER


    Quote from KjBox;794653

    Try this in cell L4 copied down


    =IF(COUNT(C4:H4)>0,"Done","Undone")


    Hello KjBox,


    Thank you very much. It works like a charm; however, I have a little problem: Staff names in Column AB are not following the same row. For this reason, I want to use a VLOOKUP or anything you can offer. In the original document, staff names are separated with some department names and they are not sorted. Please see the sample file.


    Is there a way to use vlookup or anything else to do it?

    Re: Using of VLOOKUP and ISNUMBER


    This is not very elegant but it does work!


    Put this in AC10 and copy to the other rows where column AB is a Staff Name, you cannot simply copy down because of the intervening merged cells.


    =IF(OR(ISNUMBER(VLOOKUP(AB10,$B$4:$H$14,2,0)),ISNUMBER(VLOOKUP(AB10,$B$4:$H$14,3,0)),ISNUMBER(VLOOKUP(AB10,$B$4:$H$14,4,0)),ISNUMBER(VLOOKUP(AB10,$B$4:$H$14,5,0)),ISNUMBER(VLOOKUP(AB10,$B$4:$H$14,6,0)),ISNUMBER(VLOOKUP(AB10,$B$4:$H$14,7,0)))=TRUE,"Done","Undone")

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Using of VLOOKUP and ISNUMBER


    An alternative method would be to insert an extra column in the "Task Table" which has a formula to count the numerical values for the tasks for each staff name, that column if then used in the VLOOKUP formulas in column AC


    See the attached file.

    Re: Using of VLOOKUP and ISNUMBER


    Hello KjBox,


    Both solutions are just what I desired of. Thank you much and much.


    Kind regards,


    Onexc

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke    [/SIZE]

    Re: Using of VLOOKUP and ISNUMBER


    You're welcome.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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

Register Yourself Login