Grab corresponding value from a data range

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
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
20 0 1 0 0
3
445 48 66 77 890 66
578 85 90 777 888 90
6
7
8
9
102 4 5 18 2 5

<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>
</tbody>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
20 1
3 456789 456789 4
4 7788553250100 871258910 87

<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>
</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
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
211110000
3ABCDABCD ABCD
455.0048.0077.0088.00100.00200.00300.00400.00 55.0048.0077.0088.00
5
6
7
8
9
10100.00200.00300.00400.00500.00600.00700.00800.00 100.00200.00300.00400.00

<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>
</tbody>
 
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

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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