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,
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 | ||||
---|---|---|---|---|
A | B | |||
1 | List of Id | List of Color | ||
2 | 123 | Red | ||
3 | 123_ABC | Rose | ||
4 | 456 | Blue | ||
5 | 123_DEF | Pink | ||
6 | 456_ABC | Marine | ||
7 | 789 | Yellow | ||
8 | 123_GHI | Ruby | ||
9 | 789_ABC | Cream | ||
Data |
Lookup (autopopulate) Sub Id from Main Id.xlsx | ||||
---|---|---|---|---|
A | B | |||
1 | Main Id | Colour | ||
2 | 123 | Red | ||
3 | Sub Id | |||
4 | 456 | Blue | ||
5 | #N/A | |||
6 | #N/A | |||
Result |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2,B4:B6 | B2 | =VLOOKUP(A2,Data!$A$2:$B$9,2,0) |
A4 | A4 | =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))),"") |
A5 | A5 | =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),"")) |