# how to use match function and get result as ture if condition is true then match function

1. ## how to use match function and get result as ture if condition is true then match function

hello everyone,

i have attached a screenshot and excel file with this post.

what i want to do is:
1. when we give a value in search box, it should check 'item no. reference' to match 'search box' value, if found then check all table numbers and match with column B and column C right side digits only, and if match found it should print 'in stock' as result in column E and if not found any match it should print 'out of stock'.

ie. we can see we have value 7 in 'search box B2, so it's given result as 'in stock' because match found b2,f2:f11, then it checked for match f8 row and again match found with in all table numbers with c2 row 'part no' and checked c2 and d2 right digits.

Any help will we appreciate.

Thanks & Warm Regards.

2. ## Re: how to use match function and get result as ture if condition is true then match funct

Can you try to separate distinct tables and use meaningful headers on every column?

I get the "SearchBox" is to match "Item No Reference>" but then totally lose you after that as F8 is a cell not a row and no idea what you are doing with "Parts No." and the unnamed column next to it. I thought you were saying look for the "Part No" in the "Table No" but that doesn't make sense with the example

3. ## Re: how to use match function and get result as ture if condition is true then match funct

Hi jd16,
Ok, we have value 7 in B2, which can be found in F8.
Where/how do 12 and26 in C2 and D2 fit into the equation? I can't see neither of them in the table no's.

4. ## Re: how to use match function and get result as ture if condition is true then match funct

Hello ORoos,

Originally Posted by ORoos
Hi jd16,
Ok, we have value 7 in B2, which can be found in F8.
Where/how do 12 and26 in C2 and D2 fit into the equation? I can't see neither of them in the table no's.
we will look only right side digits for match in cell C2 in D2, which is 2 in C2 and 6 In B2.

let me explain better my words, first of all we will see fiven value in search box,

so 1st given value by user is 7 so we will go with is 1st, we will check this vale match within F2:F11,

in this case we found a match with F8 so we will go through with reference number and look for if any match found for RIGHT(C2) & RIGHT(D2) only,

now as we can see RIGHT(C2) is "2" & RIGHT(D2) "6" and both are matched within G8:L8, so we can say condition value matched twice, but thats not matter,
in case if it matches only once or even more, the result should be come in column E as if matched even one time or more, then condition should true and if no match found with RIGHT(C2) & RIGHT(D2), condition should be false

5. ## Re: how to use match function and get result as ture if condition is true then match funct

Originally Posted by Sandtree
Can you try to separate distinct tables and use meaningful headers on every column?

I get the "SearchBox" is to match "Item No Reference>" but then totally lose you after that as F8 is a cell not a row and no idea what you are doing with "Parts No." and the unnamed column next to it. I thought you were saying look for the "Part No" in the "Table No" but that doesn't make sense with the example
Hi Sandtree,

lets we talk with cell references to clear the concept and leave headers for now,

column B is a user Input space so as we can see we have already two values in column B given by user which is in cell B2 nad B3,

we have to match this value first within F2:F11,
in this case match found which is already founded in F8,
now it should look for a match with either Right(C2) or Right(D2) or even in both, between G8:L8 (because our user given value matched with reference F8 so)

if a single/multiple match founded then it should print 'in stock' in E2, if no match found then 'out of stock'

that't it.

Regards

6. ## Re: how to use match function and get result as ture if condition is true then match funct

I think this statement will give you a better idea:

Conditions:
1. B2 should match within F2:F11
(I) in case no match, nothing should be happen.
(II) in case match found, it should move to 2nd condition
in this case a match found in F8 so we will look condition 2 between G8:L8

2. Match G8:L8 with Right(C2) and RIGHT(D2) **//in case in future value comes in E2,F2...so and so, it should also check them for a match with F8:L8

3. (I)in case no match found then get a result in E2 as 'Out of Stock' else move to point (II)
(II) In case one/more then one match found, it should give result as 'In Stock'

7. ## Re: how to use match function and get result as ture if condition is true then match funct

...so if you have in C2 = 12, we only take 2 and in D2 we have 26, but only take 6 (=RIGHT,1) ?

but then in row 4, we have: 2 , 99 , 09. B4 2. We find it in F4, then look for C4 = 99, so RIGHT(C4) = 9 and D4 = 09, so RIGHT(D4) = 9. Number 9 is 5 times in the table G2:L11 ...so should show as In Stock.
OR
in the above example, as we find the number 2 from cell B4 in cell F3 we are only looking for the number 9 in G3:L3... in which case it would be out of stock.

8. ## Re: how to use match function and get result as ture if condition is true then match funct

Originally Posted by ORoos
...so if you have in C2 = 12, we only take 2 and in D2 we have 26, but only take 6 (=RIGHT,1) ?
in the above example, as we find the number 2 from cell B4 in cell F3 we are only looking for the number 9 in G3:L3... in which case it would be out of stock.
absolutely right

Originally Posted by ORoos
but then in row 4, we have: 2 , 99 , 09. B4 2. We find it in F4, then look for C4 = 99, so RIGHT(C4) = 9 and D4 = 09, so RIGHT(D4) = 9. Number 9 is 5 times in the table G2:L11 ...so should show as In Stock.
OR
in the above example, as we find the number 2 from cell B4 in cell F3 we are only looking for the number 9 in G3:L3... in which case it would be out of stock.
of course 2nd option(look for 9 in G3:L3, which is not founded thats why we get result in E4 as 'out of stock'

i must say you are very close to solve it, Thanks.

9. ## Re: how to use match function and get result as ture if condition is true then match funct

yea, got the first bit working. Will get onto the second bit when i find some time...

10. ## Re: how to use match function and get result as ture if condition is true then match funct

Ok, Try this one..

11. ## Re: how to use match function and get result as ture if condition is true then match funct

Originally Posted by ORoos
Ok, Try this one..
Once again Thanks ORoos,

you are almost there, i have checked your sheet and its almost working fine accept few things, let me tell you about that(even i have attached a screenshot for reference)

one condition was one/more match found between rightC2:rightD2, so it,s working fine with multiple matches, but if you completely remove column D it will be a blank cell in D2, so in this case it should check only cell C2 for value match, and if no match founded it should come as 'out of stock' right ?
but it is giving positive result as 'in stock' even there is no match with rightC2 and between G8:L8.

let me know if above points or not very clear for you.

Thanks & Regards

12. ## Re: how to use match function and get result as ture if condition is true then match funct

All your samples in your workbook have values in column C & D. So does your screenshot. The formula works if either or both match.
The formula provided works on numbers. A blank cell is not a number.

13. ## Re: how to use match function and get result as ture if condition is true then match funct

Originally Posted by ORoos
All your samples in your workbook have values in column C & D. So does your screenshot. The formula works if either or both match.
The formula provided works on numbers. A blank cell is not a number.
exactly, so if you delete column D, there will be no values so it will be an empty cell right, according to this, now formula should only search in column C for values and send result accordingly,

but when i tried to run formula after deleting column D, there is no match between G8:L8 for rightC2 value which is 2, still formula showing found match and gives result as 'In stock'.

Please check once again after deleting all values of column D.

14. ## Re: how to use match function and get result as ture if condition is true then match funct

Originally Posted by ORoos
All your samples in your workbook have values in column C & D. So does your screenshot. The formula works if either or both match.
The formula provided works on numbers. A blank cell is not a number.

Hi ORoos
After some struggle I have modified your formula as per my needs and now it's working fine for this project

Thanks for understanding my concept very quickly, You helped me a lot and saved lots of my time so many thanks to you for that and I hope i have not irritated you too much :D

I'm hoping you will be available for further help in future.
God bless You'

15. ## Re: how to use match function and get result as ture if condition is true then match funct

Originally Posted by Sandtree
Can you try to separate distinct tables and use meaningful headers on every column?

I get the "SearchBox" is to match "Item No Reference>" but then totally lose you after that as F8 is a cell not a row and no idea what you are doing with "Parts No." and the unnamed column next to it. I thought you were saying look for the "Part No" in the "Table No" but that doesn't make sense with the example

Hi Sandtree,
Thanks for spare your precious time too

16. ## Re: how to use match function and get result as ture if condition is true then match funct

Glad you worked it out jd16.
Would be nice if you could share your solution with the members here in case they come across similar challenges.
All the best.

There are currently 1 users browsing this thread. (0 members and 1 guests)