Need help with making complex formula

locksmith55

New Member
Joined
Mar 6, 2022
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am trying to make a formula that goes from row to row and left to right to check if the cell is equal to 1. If the cell is equal to 1, then I want to print the number that's below the cell.

I added a picture to illustrate what I mean.

Appreaciate any help I can get. I have been trying to solve this for a while now with no success.

I would appreciate if the formula is not too complex.

1659212860486.png


mr excel 7 30.xlsx
ABCDEFG
1Group 1Group 2Group 3HOW I WANT IT TO LOOK
2101UPS0711AB<- I want dynamic formula that can recreate this result
3100ABC12A0777
4100UPS0711AB
5000UPS0711AB
6100UPS0711AB
7101UPS0711AB
8101ABC12A0777
9001UPS0711AB
10UPS0711ABABC12A0777ABC12A0777
11ABC12A0777
Sheet1
Cell Formulas
RangeFormula
E2E2=A10
E3E3=C10
E4E4=A10
E5E5=A10
E6E6=A10
E7E7=A10
E8E8=C10
E9E9=A10
E10E10=C10
E11E11=C10
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
How about:

Book1
ABCDE
1Group 1Group 2Group 3HOW I WANT IT TO LOOK
2101UPS0711AB
3100ABC12A0777
4100UPS0711AB
5000UPS0711AB
6100UPS0711AB
7101UPS0711AB
8101ABC12A0777
9001UPS0711AB
10UPS0711ABABC12A0777ABC12A0777
11ABC12A0777
12
Sheet3
Cell Formulas
RangeFormula
E2:E11E2=LET(rng,A2:C9,lbl,A10:C10,r,ROWS(rng),c,COLUMNS(rng),s,SEQUENCE(r*c,,0),t,INDEX(rng,INT(s/c)+1,MOD(s,c)+1),a,INDEX(lbl,MOD(s,c)+1),FILTER(a,t=1))
Dynamic array formulas.
 
Upvote 0
Solution
How about
Fluff.xlsm
ABCDE
1Group 1Group 2Group 3HOW I WANT IT TO LOOK
2101UPS0711AB
3100ABC12A0777
4100UPS0711AB
5000UPS0711AB
6100UPS0711AB
7101UPS0711AB
8101ABC12A0777
9001UPS0711AB
10UPS0711ABABC12A0777ABC12A0777
11ABC12A0777
Main
Cell Formulas
RangeFormula
E2:E11E2=LET(Rng,A2:C9,a,IFERROR(INDEX(A10:C10,IF(Rng=0,"",Rng),{1,2,3}),""),c,COLUMNS(Rng),s,SEQUENCE(ROWS(Rng)*c,,0),b,INDEX(a,INT(s/c)+1,MOD(s,c)+1),FILTER(b,b<>""))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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