Vishaal
Well-known Member
- Joined
- Mar 16, 2019
- Messages
- 533
- Office Version
- 2010
- 2007
- Platform
- Windows
- Web
Hi All,
We have the following sheet
Excel 2010 32 bit
Sheet2 from where we will check
Excel 2010 32 bit
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
We have the VBA solution but need formula
https://www.mrexcel.com/forum/excel-questions/1111163-search-match-show-result.html
We have the following sheet
Excel 2010 32 bit
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | | | | Danial | Chrix | Rocky | Ricky | log | ghun | | Danial | Chrix | Rocky | Ricky | log | ghun |
2 | | | | Ronaldo | Pamela | Donald | messy | sanju | safi | | Ronaldo | Pamela | Donald | messy | sanju | safi |
3 | | | | shika | rghu | randy | john | vijju | rodi | | shika | rghu | randy | john | vijju | rodi |
4 | | | | lovely | rick | flair | prave | archi | tina | | lovely | rick | flair | prave | archi | tina |
5 | | | | rinku | mone | rashmi | peter | novit | emli | | rinku | mone | rashmi | peter | novit | emli |
6 | | | | | | | | | | | | | | | | |
7 | | | | | | | | | | | | | | | | |
8 | 1 | | | | | | | | | | | | | | | |
9 | 2 | 17000 | 700 | Joined | Joined | Joined | Joined | Joined | Joined | | | 1 | 1 | | 1 | 1 |
10 | 3 | 83000 | 300 | Joined | | | Joined | | | | 2 | | | 2 | | |
11 | 4 | 92000 | 200 | | | | | | | | | | | | | |
12 | 5 | 42000 | 200 | Joined | | | | | | | 1 | | | | | |
13 | 6 | 69000 | 900 | | | Joined | | Joined | | | | | 1 | | 1 | |
14 | 7 | 93000 | 300 | | Joined | | | | | | | | | | | |
15 | 8 | 31000 | 100 | | Joined | | Joined | | Joined | | | | | | | |
16 | 9 | 56000 | 600 | Joined | Joined | | Joined | Joined | Joined | | | | | | 1 | |
17 | 10 | 58000 | 800 | Joined | Joined | | Joined | | Joined | | 2 | 4 | | 3 | | 3 |
18 | | | | | | | | | | | | | | | | |
19 | | | | | | | | | | | | | | | | |
20 | | | | | | | | | | | | 4 | | | | 3 |
21 | | | | | | | | | | | | Chrix | | | | ghun |
22 | | | | | | | | | | | | Pamela | | | | safi |
23 | | | | | | | | | | | | rghu | | | | rodi |
24 | | | | | | | | | | | | rick | | | | tina |
25 | | | | | | | | | | | | mone | | | | emli |
Sheet: Sheet1 |
Sheet2 from where we will check
Excel 2010 32 bit
A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
1 | | Danial | Chrix | Rocky | Ricky | log | ghun |
2 | | Ronaldo | Pamela | Donald | messy | sanju | safi |
3 | | shika | rghu | randy | john | vijju | rodi |
4 | | lovely | rick | flair | prave | archi | tina |
5 | | rinku | mone | rashmi | peter | novit | emli |
6 | | | | | | | |
7 | | | | | | | |
8 | 1 | | | | | | |
9 | 2 | | | | Yes | | |
10 | 3 | | | | | | Yes |
11 | 4 | | Yes | | | | |
12 | 5 | Yes | | | | | |
13 | 6 | | | | Yes | | Yes |
14 | 7 | | | | | | |
15 | 8 | | | Yes | | | |
16 | 9 | | | | | | |
17 | 10 | | Yes | | | | |
18 | 11 | | | | | Yes | |
Sheet: Sheet2 |
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
We have the VBA solution but need formula
https://www.mrexcel.com/forum/excel-questions/1111163-search-match-show-result.html