# Get the value and some data - VBA or Code or Formula

#### Vishaal

##### Active Member
Hi All,

We have the following two data in one sheet,

Excel 2010 32 bit
A
B
C
D
E
F
1
First Data​
2
Marks​
Ram​
Ravi​
Rozi​
Sunny​
Ahmed​
3
45​
Na​
Na​
Na​
Na​
Na​
4
65​
Na​
Na​
Na​
Na​
Na​
5
25​
Yes​
Yes​
Yes​
Yes​
Yes​
6
35​
Na​
Na​
Na​
Na​
Na​
7
26​
Yes​
Yes​
Yes​
Yes​
Na​
8
98​
Na​
Na​
Na​
Na​
Na​
9
78​
Na​
yes​
Na​
Na​
Na​
10
15​
Na​
Na​
yes​
Na​
Na​
11
45​
Na​
Na​
Na​
yes​
Na​
12
13
Total "Na" in continuation from bottom
4​
2​
1​
6​
14
15
 Sheet: Sheet1

Second sheet from where we need to get the data

Excel 2010 32 bit
I
J
K
L
M
N
1
Second Data​
2
M​
25​
85​
85​
12​
32​
3
65​
12​
54​
14​
56​
4
67​
32​
52​
17​
45​
5
58​
41​
56​
16​
87​
6
1​
yes​
7
2​
yes​
8
3​
9
4​
yes​
10
5​
11
6​
Yes​
yes​
12
7​
13
8​
14
9​
 Sheet: Sheet1

Need the following result

Excel 2010 32 bit
A
B
C
D
E
F
18
Result Is
4​
2​
1​
6​
19
6​
20
21
It’s the M Value​
25​
85​
85​
32​
22
65​
12​
54​
56​
23
67​
32​
52​
45​
24
58​
41​
56​
87​
 Sheet: Sheet1

We will check that if value of Range B13:F13 match with range I6:I14 then show the number of row where we have "yes" as given in above sheet and along with that its also show the value of "M" from that coloumn

Help pls

Last edited:

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### James006

##### Well-known Member
Hello,

In cell B21 ... you can test following

Code:
``=INDEX(Sheet2!\$J\$2:\$N\$2,MATCH("yes",INDIRECT("Sheet2!J"&MATCH(B18,Sheet2!\$I:\$I,0)):INDIRECT("Sheet2!N"&MATCH(B18,Sheet2!\$I:\$I,0)),0))``

Hope this will help

o.k. checking

Last edited:

#### Vishaal

##### Active Member
only showing Na

can u upload the working excel file if you can

#### Vishaal

##### Active Member
if you suggest we can use second sheet on first sheet

Replies
0
Views
26
Replies
0
Views
79
Replies
1
Views
51
Replies
12
Views
236
Replies
19
Views
228