+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

    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.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-02-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    51

    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. #3
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

    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. #4
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

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

    Hello ORoos,
    Thanks for your prompt reply.


    Quote Originally Posted by ORoos View Post
    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. #5
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

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

    Quote Originally Posted by Sandtree View Post
    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

  6. #6
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

    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'
    Last edited by AliGW; 06-09-2020 at 01:28 AM. Reason: Please don't quote unnecessarily!

  7. #7
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

    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. #8
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

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

    Quote Originally Posted by ORoos View Post
    ...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

    Quote Originally Posted by ORoos View Post
    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.
    Last edited by AliGW; 06-09-2020 at 01:29 AM.

  9. #9
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

    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. #10
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

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

    Ok, Try this one..
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

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

    Quote Originally Posted by ORoos View Post
    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
    Attached Images Attached Images

  12. #12
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

    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. #13
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

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

    Quote Originally Posted by ORoos View Post
    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.

  14. #14
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

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

    Quote Originally Posted by ORoos View Post
    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. #15
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

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

    Quote Originally Posted by Sandtree View Post
    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. #16
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. if Condition match with Date function
    By senthile in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-19-2019, 06:32 AM
  2. [SOLVED] function to return true/na/false when value doesn't match
    By pizzabae in forum Excel General
    Replies: 6
    Last Post: 10-13-2018, 12:19 PM
  3. Match function not work even 2 cells are true
    By saker1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-22-2016, 09:58 AM
  4. INSERT/MATCH Function - can I match and return more than a single result?
    By nickwee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2014, 06:02 AM
  5. [SOLVED] index and match function result from three way look up
    By johnodys in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-15-2013, 03:32 AM
  6. How to remove #N/A result from sum/match function?
    By Ramzes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-18-2010, 09:40 AM
  7. If Error condition with Match Function
    By T De Villiers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2006, 08:59 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1