Modify / Edit in Formula - Match and Result

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,114,260
Messages
5,546,827
Members
410,758
Latest member
Bufnercash
Top