VLOOKUP + IF statement

Question

I have an ITEM NUMBER category in column B1:B100.  I want a "YES" or "NO" to appear in column C1:C100 if that item number is listed on SHEET2, column C:C.

How do I write a formula to to accomplish this?

The "numbers" in column B is stored as text because they have leading zeros.....

Please help.......

Answer 1

If numbers are in column B, then you can't use A2. I think your problem statement says following -

1. Numbers are in B1:B100.
2. Look up B1 in Sheet2, column C and populate Yes or No in C1.
3. Same for B2 to B100 and populate C2 to C100.

Now, since your numbers are stored as text in column B, then we need to know the length of the text field. Say, your text format is 00000 (6 digits), hence 123 will appear as 00123 in column B.

Now, 2 scenarios are possible .

1. C is also in text format - Your formula would work.

=IF(ISNUMBER(MATCH(B1,'Sheet2'!C:C,0)),"Yes","No")

2. C is numeric format. Enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=IF(ISNUMBER(MATCH(B1,TEXT(Sheet2!C:C,"000000"),0)),"Yes","No")

For above formula "000000" text formatting has been applied. Please change this text formatting as per your requirement.

There are alternative formulas are possible but the formula quotes by you is the best, in my view.

Answer 2

this too:

=IF(COUNTIF(Sheet2!C:C,A2),"Yes","No")

Answer 3

This seems to work:

=IF(ISNUMBER(MATCH(A2,'Sheet2'!C:C,0)),"Yes","No")

If there are any other options please let me know.....

Answer 4

Hi,

Are the numbers in column C of sheet2 also stored as text?

Answer 5

The "numbers" in column B is stored as text because they have leading zeros......

In regard to this portion of your inquiry, you would be much better off to leave the numbers as numbers and use Custom Number Formatting to take care of the leading zero display. Type 123 in A1 then select A1 and tap Ctrl+1. When the Format Cell dialog opens, choose the Number tab and select Custom from the list down the left. Use the following for the Typer:,

00000

Click OK in the lower left and you will have the following in A1,

00123

Keeping numbers as numbers rather than storing them as text will save you many headaches down the road.