If Formula with 3 Logic didn't Work

So i'm trying to create formula based on 3 logic


On the Stock detail 07 Feb 2022 sheets column Q the logic is:
1. If cells Q7 is match with X7:BF7 then:
2. The E7:F7 must be match with 'ODC'!C2:C28
3. Column G sheet ODC on the exact row must be greater than 0
then either it's "OK" or "CHECK"
or if it didn't match criteria 2 and 3 its "-"

I'm trying to use

=IF(MATCH($Q$5,ODC!$B$1:$B$28,0),IF(AND(ISNUMBER(MATCH(E7:F7,ODC!$C:$C,0)), --(ODC!$G$1:$G$28>0)),"YES","CHECK"),"-")

This is the document
Attachment 767120

I'll be glad if someone willing to help me

Sorry sir i think i'm already attached it

The attachment is below the code

No workbook attached: follow the instructions in the yellow banner at top of the thread.

You did, but it's not a SAMPLE workbook. Far too much data there for your helper to work with. It needs to be simplified to enable testing and checking.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

The attachment link simply won't work in some browsers. You should attach file by following the instructions in the yellow banner at the top of the page.

Your sample workbook should also include some MANUALLY CALCULATED expected results: sharing a non-working formula does not get us very far.

Had you followed advice given, you'd have had a solution by now. As it stands, your expectation that you should NOT have to amend anything is precisely the reason why you haven't yet been helped. Your choice, of course.

This is the new document. I've cut the Stock detail sheets to 20 rows

Originally Posted by AliGW

The attachment link simply won't work in some browsers. ...

Please help to check Stock detail 07 Feb 2022 on column Q maam

I can't see where you have shared the results you are EXPECTING. Please add these. Thanks.

Attachment updated, but not with the information requested. I'll try again.

We need to know what results you want here:

AliGW on MS365 Insider (Windows) 64 bit

	Q
5	W10
6	ODC
7	CHECK
8	CHECK
9	CHECK
10	CHECK
11	CHECK
12	CHECK
13	CHECK
14	CHECK
15	CHECK
16	CHECK
17	CHECK
18	CHECK
19	CHECK
20	CHECK

Sheet: Stock detail 07 Feb 2022

DO NOT show us the results of a formula that you know is not working. Instead, manually type in the results you want to see for the 20 rows of data provided. Thanks.

Someone else will pick this up - I've run out of time for now.

See post #13. I asked for expected results, not a repeat of the criteria.

I'm expecting your help ma'am,

Unfortunately you have wasted the best part of three hours with your unwillingness to provide simple sample data. I have now run out of time - sorry. Someone else will take it from here, but they will need the information I am asking for.

Sorry i don't get what you asked. I'm not native in english so there's some expression that i don't understand.

In post #13 I showed you what you have given us in your sample workbook. Do you see that?

This is NOT what we want to see. In column Q you need to type MANUALLY (NO formula!) the results you are expecting.

Do this now and attach the amended copy of your workbook to your next post.

Your English is much better than my bahasa Indonesia.

I hope you get this resolved. I have to go out now.

I see. I expecting if i enter the value that matches with store on ODC on Stock Detail Q5, It will automatically turn Q7 below to the "OK", "CHECK" or "-" value.

Try

=IF(MATCH($Q$5,ODC!$B$1:$B$28,0),IF(AND(ISNUMBER(MATCH(F7,ODC!$C:$C,0)), --(ODC!$G$1:$G$28>0)),"YES","CHECK"),"-")

NOTE: column C of ODC only contains "MENSPORT" (Sub-category) : there is no "Category" (PMSHOES)

If BOTH are present the formula should be:

=IF(MATCH($Q$5,ODC!$B$1:$B$28,0),IF(AND(ISNUMBER(MATCH(E7&F7,ODC!$C:$C,0)), --(ODC!$G$1:$G$28>0)),"YES","CHECK"),"-")

and column C will be PMSHOESMENSPORT (no spaces or separator).

Originally Posted by JohnTopley

Try

=IF(MATCH($Q$5,ODC!$B$1:$B$28,0),IF(AND(ISNUMBER(MATCH(F7,ODC!$C:$C,0)), --(ODC!$G$1:$G$28>0)),"YES","CHECK"),"-")

NOTE: column C of ODC only contains "MENSPORT" (Sub-category) : there is no "Category" (PMSHOES)

If BOTH are present the formula should be:

=IF(MATCH($Q$5,ODC!$B$1:$B$28,0),IF(AND(ISNUMBER(MATCH(E7&F7,ODC!$C:$C,0)), --(ODC!$G$1:$G$28>0)),"YES","CHECK"),"-")

and column C will be PMSHOESMENSPORT (no spaces or separator).

it doesn't work as i want,
but i think i already knows what's the problem.
the column G on ODC is not on the exact row as the store that inputted in Q5.

i tried to change the third logic ODC!$G15>0 and the cells turn into "YES". so we need to put the third logic on the specified row that matches with the store on Q5

1. Pleae stop quoting posts - it is not necessary and wastes space in your thread.
2. For the LAST time, what are the RESULTS in cells Q5 to Q20 that you want to see? EXACT RESULTS, not an explanation of how they are to be worked out. At the moment column Q says CHECK in every cell - change the cells that should NOT be CHECK to the correct result and attach the workbook again.

I really don't know how many more ways I can explain this to you. Anyway, really got to go now.

We do NOT need to see the results of a failing formula.

Which of these cells should say "OK" and which should say "-"???

AliGW on MS365 Insider (Windows) 64 bit

	Q
5	W10
6	ODC
7	CHECK
8	CHECK
9	CHECK
10	CHECK
11	CHECK
12	CHECK
13	CHECK
14	CHECK
15	CHECK
16	CHECK
17	CHECK
18	CHECK
19	CHECK
20	CHECK

Sheet: Stock detail 07 Feb 2022

Please mark as SOLVED: See "Thread Tools" at top of thread.

there's no example for "-" because i already made the category to only MENSPORT

For future reference: in a sample workbook you should include examples of ALL possible outcomes.

This will not work....

MATCH(OR(E22,F22),'C:C,0)

try this ...

OR(MATCHE22,'C:C,0),MATCH(F22,'C:C,0)),

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.