Modify / Edit in Formula - Match and Result

[Vishaal]

Hi All,

We have the following sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][th]

O​

[/th][th]

P​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Danial​

[/td][td]

Chrix​

[/td][td]

Rocky​

[/td][td]

Ricky​

[/td][td]

log​

[/td][td]

ghun​

[/td][td]

​

[/td][td]

Danial​

[/td][td]

Chrix​

[/td][td]

Rocky​

[/td][td]

Ricky​

[/td][td]

log​

[/td][td]

ghun​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Ronaldo​

[/td][td]

Pamela​

[/td][td]

Donald​

[/td][td]

messy​

[/td][td]

sanju​

[/td][td]

safi​

[/td][td]

​

[/td][td]

Ronaldo​

[/td][td]

Pamela​

[/td][td]

Donald​

[/td][td]

messy​

[/td][td]

sanju​

[/td][td]

safi​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

shika​

[/td][td]

rghu​

[/td][td]

randy​

[/td][td]

john​

[/td][td]

vijju​

[/td][td]

rodi​

[/td][td]

​

[/td][td]

shika​

[/td][td]

rghu​

[/td][td]

randy​

[/td][td]

john​

[/td][td]

vijju​

[/td][td]

rodi​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

lovely​

[/td][td]

rick​

[/td][td]

flair​

[/td][td]

prave​

[/td][td]

archi​

[/td][td]

tina​

[/td][td]

​

[/td][td]

lovely​

[/td][td]

rick​

[/td][td]

flair​

[/td][td]

prave​

[/td][td]

archi​

[/td][td]

tina​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

rinku​

[/td][td]

mone​

[/td][td]

rashmi​

[/td][td]

peter​

[/td][td]

novit​

[/td][td]

emli​

[/td][td]

​

[/td][td]

rinku​

[/td][td]

mone​

[/td][td]

rashmi​

[/td][td]

peter​

[/td][td]

novit​

[/td][td]

emli​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

2​

[/td][td]

17000​

[/td][td]

700​

[/td][td]

Joined​

[/td][td]

Joined​

[/td][td]

Joined​

[/td][td]

Joined​

[/td][td]

Joined​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

3​

[/td][td]

83000​

[/td][td]

300​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

4​

[/td][td]

92000​

[/td][td]

200​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

5​

[/td][td]

42000​

[/td][td]

200​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

6​

[/td][td]

69000​

[/td][td]

900​

[/td][td]

​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

7​

[/td][td]

93000​

[/td][td]

300​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

8​

[/td][td]

31000​

[/td][td]

100​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

9​

[/td][td]

56000​

[/td][td]

600​

[/td][td]

Joined​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

Joined​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

10​

[/td][td]

58000​

[/td][td]

800​

[/td][td]

Joined​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

Joined​

[/td][td]

​

[/td][td]

2​

[/td][td]

4​

[/td][td]

​

[/td][td]

3​

[/td][td]

​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td=bgcolor:#95B3D7]

​

[/td][td=bgcolor:#95B3D7]

4​

[/td][td=bgcolor:#95B3D7]

​

[/td][td=bgcolor:#95B3D7]

​

[/td][td=bgcolor:#95B3D7]

​

[/td][td=bgcolor:#95B3D7]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

Chrix​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

ghun​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

Pamela​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

safi​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

rghu​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

rodi​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

rick​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

tina​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

mone​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

​

[/td][td=bgcolor:#FCD5B4]

emli​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Sheet2 from where we will check
Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

​

[/td][td]

Danial​

[/td][td]

Chrix​

[/td][td]

Rocky​

[/td][td]

Ricky​

[/td][td]

log​

[/td][td]

ghun​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

​

[/td][td]

Ronaldo​

[/td][td]

Pamela​

[/td][td]

Donald​

[/td][td]

messy​

[/td][td]

sanju​

[/td][td]

safi​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

​

[/td][td]

shika​

[/td][td]

rghu​

[/td][td]

randy​

[/td][td]

john​

[/td][td]

vijju​

[/td][td]

rodi​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

​

[/td][td]

lovely​

[/td][td]

rick​

[/td][td]

flair​

[/td][td]

prave​

[/td][td]

archi​

[/td][td]

tina​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

​

[/td][td]

rinku​

[/td][td]

mone​

[/td][td]

rashmi​

[/td][td]

peter​

[/td][td]

novit​

[/td][td]

emli​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Yes​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

4​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

5​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

6​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

Yes​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

7​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

8​

[/td][td]

​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

9​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

10​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

11​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Yes​

[/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

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

 

[Vishaal]

Cross-posted questions
https://www.excelforum.com/excel-fo...-some-condition-and-get-the-match-result.html post#13

If anything wrong, pls delete

 

[Vishaal]

problem solved