Grab corresponding value from a data range

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
My Data Range is B4:L10 consisting of 5 columns (B,E,H,J & L)as shown
plus
B2, E2, H2, J2 & L2 is either of 1 or 0

Output required in N4:N10 as
‘grab’ the value from the corresponding row cell of B4:L10 which has 1 in B2:L2.

How to accomplish?
Thanks in advance
I am using Excel 2007
Sheet1

BCDEFGHIJKLMN

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]48[/TD]

[TD="align: right"]66[/TD]

[TD="align: right"]77[/TD]

[TD="align: right"]890[/TD]

[TD="bgcolor: #ffff00, align: right"]66[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]78[/TD]

[TD="align: right"]85[/TD]

[TD="align: right"]90[/TD]

[TD="align: right"]777[/TD]

[TD="align: right"]888[/TD]

[TD="bgcolor: #ffff00, align: right"]90[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]18[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #ffff00, align: right"]5[/TD]

</tbody>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Copy N4 down:
Excel Workbook
BCDEFGHIJKLMN
200100
3
44548667789066
578859077788890
Sheet4
 
Upvote 0
In N4, fill down:
=IF(COUNTA(B4:L4)<>0,OFFSET(A4,0,MATCH(1,$B$2:$L$2,0)),"")

This could be dressed up a bit to check if the value was "" rather than the whole row.
 
Last edited:
Upvote 0
No this is not required. Probably I missed a lot….please bear…

My data is in more than 1 range like
Range 1: C4:H100 having a ‘Commander cell’ B2 which is EITHER of 1 or 0 PLUS ‘Sub-Commander cells’ C3:H3 which have values
Range 2: M4:R100 having a ‘Commander cell’ L2 which is EITHER of 1 or 0 PLUS ‘Sub-Commander cells’ M3:R3 which have values

At a time ONLY 1 ‘Commander cell’ would be 1

Output is required in U4:U100 as
‘Grab’ value from cell which MATCHES EXACTLY U3 with ‘Sub-Commander cells’ HAVING ‘Commander cell’ as 1
Sheet1

BCDEFGHIJKLMNOPQRSTU

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 6px"><col style="WIDTH: 6px"><col style="WIDTH: 6px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 6px"><col style="WIDTH: 6px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] , align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] , align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] , align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]87[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]87[/TD]

</tbody>
 
Upvote 0
Please allow me to add more conditions to my post#4

There are 10 Ranges having a ‘Commander cell’ (which is EITHER of 1 or 0) PLUS ‘Sub-Commander cells’ (which have values)

Each of the TEN ranges can be ‘identified’ for output through values like A,B,C,D,E,F,G,H,I & J in C1:H1; M1:R1; & so on
i.e.

C1=A, D1=A, E1=A, F1=A, G1=A, H1=A
M1=B, N1=B, O1=B, P1=B, Q1=B, R1=B

Feel free to ask for more clarifications
 
Upvote 0
Kenneth Hobson said:
You lost me.

I’ll try to make it SIMPLEST

Data Range B4:I10 containing values
B3:I3 contains a ‘header’ (A, B, C or D)
B2:I2 is EITHER of 1 or 0

Output is required in K4:N10 as

‘grab’ values from the corresponding row of B4:I10 where K3:N3 MATCHES EXACTLY with B3:I3 HAVING B2:I2 as 1
Sheet1

BCDEFGHIJKLMN

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 12px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]55.00[/TD]
[TD="align: right"]48.00[/TD]
[TD="align: right"]77.00[/TD]
[TD="align: right"]88.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]300.00[/TD]
[TD="align: right"]400.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]55.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]48.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]77.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]88.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]300.00[/TD]
[TD="align: right"]400.00[/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"]600.00[/TD]
[TD="align: right"]700.00[/TD]
[TD="align: right"]800.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]100.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]200.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]300.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]400.00[/TD]

</tbody>
Click to expand...
 
Upvote 0
Try:


Book1
ABCDEFGHIJKLMN
1211110000
23ABCDABCDABCD
345548778810020030040055487788
45
56
67
78
89
910100200300400500600700800100200300400
Sheet7
Cell Formulas
RangeFormula
K3=IF(B3="","",SUMIFS($B3:$I3,$B$1:$I$1,1,$B$2:$I$2,K$2))


Put the formula in K3, then copy to the right and down as needed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Extract the require data from Sheet1 to Sheet2 (Total Consumption For an Item for a period in Sheet1 For Several Items) in another sheet2 get in deta
2
Replies
10
Views
220
DanteAmor
DanteAmor
N
  • Question Question
Convert Data from multiple Rows to one Row with corresponding Value in Columns
Replies
9
Views
607
Kerryx
K
A
  • Solved
Create Named Range for each column based on column heading text.
Replies
5
Views
311
Cubist
Cubist
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
60
aurelius33
A
Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
422
Alex Blakenburg
A

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top