How to autopopulate sub id from main id

Haryo

New Member
Joined
Jul 19, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
I'm new for excel formulas and dont know much about it (still learning/self taught).
I sincerely appreciated your time for helping me solving this problem.

Here is my problem,
How to autofill/populate Sub Id (column A4;A6) from Main Id (column A2).
I has using 4 formulas, and have "blank" value on column A4:A6 (Quick Review tab).
Some formulas have result/value but not as I want.

Thank You,

Lookup (autopopulate) Sub Id from Main Id.xlsx
AB
1List of IdList of Color
2123Red
3123_ABCRose
4456Blue
5123_DEFPink
6456_ABCMarine
7789Yellow
8123_GHIRuby
9789_ABCCream
Data
Lookup (autopopulate) Sub Id from Main Id.xlsx
AB
1Main IdColour
2123Red
3Sub Id
4456Blue
5 #N/A
6#N/A
Result
Cell Formulas
RangeFormula
B2,B4:B6B2=VLOOKUP(A2,Data!$A$2:$B$9,2,0)
A4A4=IFERROR(INDEX(Data!$A$2:$A$9,SMALL(IF(ISNUMBER(SEARCH($A$2,Data!$A$2:$A$9)),COLUMN(Data!$A$2:$A$9)-MIN(COLUMN(Data!$A$2:$A$9))+1),COLUMN($1:1))),"")
A5A5=IF($A$2="","",IFERROR(INDEX(Data!$A$2:$B$9,SMALL(IF(ISNUMBER(SEARCH($A$2,Data!$A$2:$B$9)),ROW(Data!$A$2:$B$9)-ROW(Data!$A$2:$B$9)),ROW(Data!$A$2:$B$9)),1),""))
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi & welcome to MrExcel.
How about in A4 copied down
=IFERROR(INDEX(Data!$A$2:$A$9,AGGREGATE(15,6,(ROW(Data!$A$2:$A$9)-ROW(Data!$A$2)+1)/(ISNUMBER(SEARCH($A$2&"_",Data!$A$2:$A$9))),ROWS(A$4:A4))),"")
 
Upvote 0
Hi & welcome to MrExcel.
How about in A4 copied down
=IFERROR(INDEX(Data!$A$2:$A$9,AGGREGATE(15,6,(ROW(Data!$A$2:$A$9)-ROW(Data!$A$2)+1)/(ISNUMBER(SEARCH($A$2&"_",Data!$A$2:$A$9))),ROWS(A$4:A4))),"")

Lookup (autopopulate) Sub Id from Main Id.xlsx
AB
1Main IdColour
2123Red
3Sub Id
4123_ABCRose
5123_DEFPink
6123_GHIRuby
Result
Cell Formulas
RangeFormula
B2,B4:B6B2=VLOOKUP(A2,Data!$A$2:$B$9,2,0)
A4:A6A4=IFERROR(INDEX(Data!$A$2:$A$9,AGGREGATE(15,6,(ROW(Data!$A$2:$A$9)-ROW(Data!$A$2)+1)/(ISNUMBER(SEARCH($A$2&"_",Data!$A$2:$A$9))),ROWS(A$4:A4))),"")



Thank You very much it worked.
And if you dont mind can you explain in part AGGREGATE(15,6,(ROW(Data!$A$2:$A$9)-ROW(Data!$A$2)
 
Upvote 0
The 15 tells the aggregate function to work work like the small function & the 6 tells it to ignore errors, so it works in the same way as a small/if, except you don't need to confirm it with Ctrl Shift Enter.
 
Upvote 0
The 15 tells the aggregate function to work work like the small function & the 6 tells it to ignore errors, so it works in the same way as a small/if, except you don't need to confirm it with Ctrl Shift Enter.

so thats how the formula works.
thank you very much, i get it clear now
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
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