+ Reply to Thread
Results 1 to 20 of 20

Match from sheet 1 to sheet 2 some condition and get the match result

  1. #1
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Match from sheet 1 to sheet 2 some condition and get the match result

    Hi,

    We want to check, match and get data from two sheet as per attached excel sheet

    We will check and match if Range K8:P8 will match Range A8:A18 and if there is Yes in B8:B18 then it will copy the S.No. and heading and paste in sheet1 column,
    Again formula will check the with the range K9:P9 and then next and next and next

    Sheet1 from where we will compare and match and get the result, please check the sheet1 heading Result

    help pls
    Attached Files Attached Files
    Last edited by Ajay45822; 11-01-2019 at 06:08 AM.

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    You are going to have to explain again. I cannot follow your explanation, at all.
    Glenn



  3. #3
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Quote Originally Posted by Glenn Kennedy View Post
    You are going to have to explain again. I cannot follow your explanation, at all.
    Sorry Glenn, i have remove the lines.......help pls

  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    You have added nothing to your explanation . For a START, which sheet s are you referring to here:

    We will check and match if Range K8:P8 will match Range A8:A18 and if there is Yes in B8:B18 then it will copy the S.No. and heading and paste in sheet1 column,
    Again formula will check the with the range K9:P9 and then next and next and next

  5. #5
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,024

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Glenn, I have no idea on how to implement this, but this is what he is looking for. in Sheet 1, find last row containing data in range K:P. For each number found in there look into Sheet2 in rows 8:18. If the adjacent cells for each number from last row Sheet1 that match yes in Sheet2 rows 8:18, return all the headings from row 1:5 same column, below it.Or at least that is what I think that he wants. Probably VBA, not formula, as there will be more rows filled in Sheet1.
    Click the * to say thanks.

  6. #6
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    PaulM, You may be right. But my view is if folk want us to help them... they need to do a bit of work, too, to make their requirement clear.

  7. #7
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Quote Originally Posted by PaulM100 View Post
    Glenn, I have no idea on how to implement this, but this is what he is looking for. in Sheet 1, find last row containing data in range K:P. For each number found in there look into Sheet2 in rows 8:18. If the adjacent cells for each number from last row Sheet1 that match yes in Sheet2 rows 8:18, return all the headings from row 1:5 same column, below it.Or at least that is what I think that he wants. Probably VBA, not formula, as there will be more rows filled in Sheet1.
    Yes, you are right PaulM100

  8. #8
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Quote Originally Posted by Glenn Kennedy View Post
    PaulM, You may be right. But my view is if folk want us to help them... they need to do a bit of work, too, to make their requirement clear.
    You are right Glenn and sorry for that from next time we will put the sheet with all the steps so the all the points will be clear

    Heartily Sorry to you

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    In K20, copied across:

    =IF(LOOKUP(LOOKUP(2,1/(K$1:K$19<>""),K$1:K$19),Sheet2!$A$8:$A$18,Sheet2!B$8:B$18)="Yes",LOOKUP(2,1/(K$1:K$19<>""),K$1:K$19),"")

    In K21, copied across and dnown:
    =IF(LOOKUP(LOOKUP(2,1/(K$1:K$19<>""),K$1:K$19),Sheet2!$A$8:$A$18,Sheet2!B$8:B$18)="Yes",K1,"")
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Quote Originally Posted by Glenn Kennedy View Post
    In K20, copied across:

    =IF(LOOKUP(LOOKUP(2,1/(K$1:K$19<>""),K$1:K$19),Sheet2!$A$8:$A$18,Sheet2!B$8:B$18)="Yes",LOOKUP(2,1/(K$1:K$19<>""),K$1:K$19),"")

    In K21, copied across and dnown:
    =IF(LOOKUP(LOOKUP(2,1/(K$1:K$19<>""),K$1:K$19),Sheet2!$A$8:$A$18,Sheet2!B$8:B$18)="Yes",K1,"")
    Thanks Glenn Bro,

    its working, we will also check it on original data and ask you if any other help required, but this time all the points will be clear

    Thanks bro

  11. #11
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    thanks Glenn Kennedy Ji

    its working fine

    thanks for your help and efforts for us

  12. #12
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Smile Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Thanks bro....
    Last edited by Ajay45822; 11-07-2019 at 04:09 AM.

  13. #13
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Hi,

    need a modification in formula, if possible check and help pls

    We have used the formula in
    (1)
    K20 =IF(LOOKUP(LOOKUP(2,1/(K$1:K$19<>""),K$1:K$19),Sheet2!$A$8:$A$18,Sheet2!B$8:B$18)="Yes",LOOKUP(2,1/(K$1:K$19<>""),K$1:K$19),"")

    and
    (2) K21 =IF(LOOKUP(LOOKUP(2,1/(K$1:K$19<>""),K$1:K$19),Sheet2!$A$8:$A$18,Sheet2!B$8:B$18)="Yes",K1,"")

    We have only one problem, Formula 1 will check in all column "Last Filled Cell"
    but we want it will take only Last filled row, we mean

    K20 will check its value on A8:A18 and if find match it will also check B8:B18 for Yes and if both condition matched it show the value on sheet1
    L20 will check its value on A8:A18 and if find match it will also check B8:B18 for Yes and if both condition matched it show the value on sheet1
    M20 will check its value on A8:A18 and if find match it will also check B8:B18 for Yes and if both condition matched it show the value on sheet1
    N20 will check its value on A8:A18 and if find match it will also check B8:B18 for Yes and if both condition matched it show the value on sheet1
    O20 will check its value on A8:A18 and if find match it will also check B8:B18 for Yes and if both condition matched it show the value on sheet1
    P20 will check its value on A8:A18 and if find match it will also check B8:B18 for Yes and if both condition matched it show the value on sheet1

    When we fill the data on row 21, it will check only row 21 values

    We are working with your formula, applying the above condition, we delete all the data K9:P19 if we are checking row 20
    We are working with your formula, applying the above condition, we delete all the data K9:P20 if we are checking row 21

    please check the attached file in that we have deleted the data and checked with only last filled row.

    we have asked this question here also
    https://www.mrexcel.com/forum/excel-...ch-result.html
    Attached Files Attached Files
    Last edited by Ajay45822; 11-08-2019 at 03:44 AM. Reason: missed something

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,714

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Perhaps it would be easier to understand what you want if you could tell us the values that you expect to appear in K20:P20.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  15. #15
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    sorry we are not good in English

    trying the explain you,

    solution provided by Glenn Kennedy in post #9 is working for me, but its search, match and show the value on last filled column basis, he have given the solution as we have asked in question, we want only one modification

    and that is search and match will be based on last filled row,

    we are updating data daily, suppose we have updated data yesterday on K20:P20
    now today we will update on K21:P21
    tomorrow we will update on K22:P22

    so formula will check from last filled row

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,714

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Assuming that you are inserting rows in sheet 1 and adding to the existing rows in sheet 2, try the following:
    Modify the first formula to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Modify the second formula to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  17. #17
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Thanks for the solution,

    please check the attached sheet and update.
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,714

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Replace the formula in cell G24 with the following and then copy across:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  19. #19
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    77

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    Thanks bro,

    you have done a great job for us

    we have modified the formula for G25 according to your formula, now we are using in G25 following formula
    =IFERROR(IF(LOOKUP(LOOKUP(2,1/($A$14:$A$23<>""),G$14:G$23),Sheet2!$A$8:$A$18,Sheet2!B$8:B$18)="Yes",G14,""),"")

    pls confirm modification is o.k. (formula is working fine for me, but pls check and suggest its o.k)

    Thanks for your efforts and help

    great man

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,714

    Re: Match from sheet 1 to sheet 2 some condition and get the match result

    It seems to work fine, however I wonder if it is really necessary to go back through the lookup process that is already done in G24.
    Seems that if G24 has a value then G25 should too and if G24 is empty then G25 should be also.
    If that is true then G25 could be: =IF(G$24<>"",G14,"")
    Let us know if you have any questions.

+ 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. [SOLVED] Index and match horizontally from one sheet and result vertically to other sheet!
    By Ktripat1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2018, 03:52 AM
  2. how to combine hlookup with IF to match values from different sheets
    By genetist in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-19-2014, 09:41 AM
  3. [SOLVED] Find match from 2 columns in 2 sheets and display result in the second sheet
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-15-2013, 05:59 PM
  4. [SOLVED] Compare text in column B, C match with sheet 2 A:A and get result in E
    By Daniel Chang in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2013, 02:21 AM
  5. Replies: 0
    Last Post: 10-25-2012, 03:38 PM
  6. match A3 to range then copy result into another sheet..
    By hot in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2012, 07:59 PM
  7. Match column from sheet 2 to sheet 1 and copy rows if match exists.
    By GravityInvert in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2008, 01:42 PM

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