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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
Create Named Range for each column based on column heading text.
Replies
5
Views
302
Cubist
Cubist
Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
373
Alex Blakenburg
A
Jeffrey Mahoney
  • Solved
Get Items From Descriptions Based on List
Replies
5
Views
396
Fluff
Fluff
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
196
StephenCrump
S
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
183
Pbobas
P

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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