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.

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 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.

Hello ORoos,
Thanks for your prompt reply.

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

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,
Thnaks for your prompt reply.

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,

let's talk about B2 first,
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

...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.

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

Ok, Try this one..

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

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.

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.


Thanks in Advance.

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'

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

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.