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

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

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

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

Originally Posted by Glenn Kennedy
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. ## 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. ## 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.

6. ## 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. ## Re: Match from sheet 1 to sheet 2 some condition and get the match result

Originally Posted by PaulM100
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. ## Re: Match from sheet 1 to sheet 2 some condition and get the match result

Originally Posted by Glenn Kennedy
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. ## 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,"")

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

Originally Posted by Glenn Kennedy
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. ## 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. ## Re: Match from sheet 1 to sheet 2 some condition and get the match result

Thanks bro....

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

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

15. ## 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. ## 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:

Modify the second formula to read:
Formula:

Let us know if you have any questions.

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

18. ## 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:

Let us know if you have any questions.

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

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

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