If, and, vlookup, istext

Hello all,

I am trying to combine several functions to do the following -

Lookup data on another worksheet to find a match, once the match is found, check another column on the same worksheet and see if it contains ANY text. IF it contains text, return a value of "Complete", if it does NOT contain text, return a value of "WIP"

The current formula is not working and is only returning "No" and not picking up the text in the box. Attached is my example sheet. All of my columns/tables have to stay as they are so I cannot add or take away any columns/tables.

Thank you!

In B3

=IF(ISTEXT(VLOOKUP(A2,Table1,2,0)),"Complete","WIP")

Those seem to both looking for text in the 1st column on the second sheet, but I need to find the match, THEN look up text in the second column and return the values based on their being text/a blank.

Originally Posted by 6StringJazzer

First, your VLOOKUP is looking in Table2, which is on Sheet1. I think you mean to look in Table1, which is in Sheet2.

Second, the formula you have does not look quite like what you described; it has Yes and No, not Complete or WIP.

I do not understand what you are trying to do with this part of your formula: (ISTEXT(Sheet2!B:B="*"))

Based on your description above here is what I think you want:

Formula:

Please Login or Register  to view this content.

Note that the third argument for VLOOKUP is 2, which means pull the answer out of the second column of the table.

What result do you want if the job number is not found on Sheet2?

So the Job numbers are on two sheets, but not in the same order. I'm trying to have it first find it's match, then look down to another column and see if there is any text in the cell on the associated row. IF there is text, I want it to return "Complete" and if there is no text, return "WIP"

Sorry for the confusion. I did the example sheet after I wrote it all out and didn't realize I had written a detailed explanation. My sheet uses the complete and WIP, and I just put yes and no on the example to simplify it. Does that help clarify at all what I'm trying to accomplish?

Originally Posted by Richard Buttrey

But the formulae you've been given do exactly that, the only variabe being whether you want a Yes/No or a complete/WIP answer.

The MATCH you mention is accomplished by the VLOOKUP which looks in column 1 of Table 1 and returns the value in the 2nd column. If that is indeed text then they return "Complete", or "Yes", depending on which variations you want.

However your #5 is confusing since it now mentions you seemingly want the text value instead.

It would help if you manually add the actual results you expect.

If you look at the example I uploaded, on Sheet 2 I've put some random text next to job number 465 under the column "Data". The result I want would then show YES next to job number 456 on the Sheet 1. Does that help?

IF there is text, I want it to return "Complete" and if there is no text, return "WIP"

=IF(ISTEXT(VLOOKUP(A2,Table1,2,0)),"Complete","WIP")

or


=IF(ISTEXT(VLOOKUP(A2,Table1,2,0)),"Yes","No")

Originally Posted by JohnTopley

=IF(ISTEXT(VLOOKUP(A2,Table1,2,0)),"Complete","WIP")

or


=IF(ISTEXT(VLOOKUP(A2,Table1,2,0)),"Yes","No")

I'm sorry I've done such a horrible job explaining this! If you look at the example I uploaded, on Sheet 2 I've put some random text next to job number 465 under the column "Data". The result I want would then show YES next to job number 456 on the Sheet 1. Does that help?

Have you actual TRIED any formulae provided???

Originally Posted by JohnTopley

Have you actual TRIED any formulae provided???

I have tried them all. None of them return any data.

Well they do in your test file

Originally Posted by JohnTopley

Well they do in your test file

Okay, maybe it's something I'm doing wrong. Thanks. I will play with it and try to find out what I'm doing wrong. Out of curiosity, what is the "0" doing in the Vlookup formula? Looking for blanks? Thanks again.

You can use "0" or FALSE so it looks for an exact match

I FINALLY figured out why it's not working. The job number column on the second sheet is not to the far left. I remember now that Vlookup can only look to its left. I have attached a new example to show what it's doing with the current formula. No idea how to fix it except maybe using Index/Match?

Originally Posted by Richard Buttrey

Yes, as you've discovered a VLOOKUP must use the first column and you're correct in that an INDEX(MATCH()) formula will be needed.
e.g.

Formula:

Please Login or Register  to view this content.

THANK YOU!!!!!!! I appreciate it so much. I've got to learn more about INDEX/MATCH, as Vlookup seems to be more limited. Thanks again!