# 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
1. =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,

Kind regards,

Onexc

## Files

• sample.xlsx

[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

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?

## Files

• sample2.xlsx

[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

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.

## Files

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

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.