# Modify / Edit in Formula - Match and Result

#### Vishaal

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

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

problem solved

Replies
23
Views
481
Replies
2
Views
472
Replies
5
Views
79
Replies
11
Views
136
Replies
23
Views
175