# Thread: Help needed: cell should switch to Y or N, but formula doesn't work

1. ## Help needed: cell should switch to Y or N, but formula doesn't work

Hi all,

similar one of my last topics (http://www.excelguru.ca/forums/showt...of-other-cells ), I am working on an excelsheet that, via the import-function of our webshop, automatically hides the items on our webshop that are not in stock with our supplier. I would combine our own export of products (columns A-H) with the inventory of the supplier (columns L-P). Unfortunately the layout of the inventory of this supplier is different than the last.

Now what I am searching for is a formula that does the following:
If the value of G2 is found in column L, and the corresponding cell O* reads "0", cell I2 (where the code should be entered) should read "Y". If G2 is found in column L, and the corresponding cell O reads "1", or a value above 1, cell I2 should read "N".

*so if the value of G2 is found in cell L2, the corresponding cell O is O2

I made the following formula, based on the formula you kindly helped me with in my last topic:
IFERROR(IF(INDEX(O:O;MATCH(F3;K:K;0))=0;"Y";"N");"")

but this formula works only partly, for it also puts a Y in cells that have a corresponding value higher than 0. What am I doing wrong?

Again, thank you very much for your help, excel-wizards :-)

Best wishes,

Brecht

2. That formula should only work if the value in column O where the match occurs in K is exactly equal to 0.

Can you post a sample workbook showing that it is not doing this?

3. Hi (again) NBVC,

I believe this should work:

Example sheet 2.xlsx

Best wishes,

Brecht

4. Your values in column K are actually formatted as text, this is why you are getting an error for the match, and because your IFERROR() says to return a "Y" if an error occurs, you are getting all "Y's".

If you intend to leave column K as is, then you need to adjust the formula to accomodate and convert the column F value in situ:

=IFERROR(IF(INDEX(O:O;MATCH(""&F2;K:K;0))=0;"Y";"N");"Y")

or you can convert column O to numeric, by selecting the column, going to Data|Text to Columns and simply clicking Finish.

5. Thank you for the reply!
I believe I have to keep K as standard, because otherwise excel will insert a . to mark a thousand, whereas in this document the . is used to distinguish between the main article number and the product option.
I adjusted the formula and it works now! Thank you so much again!

6. Hi,

sorry for troubling you again on this question. I thought the formula worked, but it appears it doesn't make an exact match. So for example, when I have an article number 572, and there is an article number 1572 in column L, it gets the status of this cell, instead of the status matching with the cell containing 572. I hope I explain it clearly.
What did I do wrong?

7. Can you show a sample workbook with the problem?

8. example sheet 3.xls

Here you are.
Cell F2 should have an exact match in cell K17, directing us to cell O17, and setting cell I2 to "N", but this doesn't happen.
I checked whether cell I2 would switch to "N" if you'd place a number higher than 0 in O2 (and the following cells), but that doesn't work either.

9. As I had mentioned before, your column K and your F2 entry are not consistent as far as format. The 1122 in K17 is entered as a number, but the formula was written to accomodate column K being entered as text.

If you format column K as TEXT, then re-enter the 1122 in K17, you should see the "Y" in I2.

10. I am sorry, I misread your original instructions. Now it does work alright.
Thank you for your patience & guidance.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•