How to lookup in merge cell
- Thread starter Debraj
- Start date
bobhc
Excel Ninja
Good day Debraj
Avoid merging cells
Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.
For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.
In addition, not all cell formats stick once you unmerge a cell.
You can't sort a column with merged cells.
You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.
You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!
Merging cells in columns and rows could lead to data loss, bad thing.
Formulas and Functions that refer to merged cells will not work, bad thing.
Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.
Center Across Selection is a far better alternative to merging.
To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.
You will get the desired look you want but without the merged cells problems.
Avoid merging cells
Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.
For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.
In addition, not all cell formats stick once you unmerge a cell.
You can't sort a column with merged cells.
You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.
You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!
Merging cells in columns and rows could lead to data loss, bad thing.
Formulas and Functions that refer to merged cells will not work, bad thing.
Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.
Center Across Selection is a far better alternative to merging.
To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.
You will get the desired look you want but without the merged cells problems.
Somendra Misra
Excel Ninja
Hi Debraj,
A bit longer formula but working, (CSE formula) in E2 and copy down:
=INDEX($B$2:$B$16,SMALL(IF(LOOKUP(ROW($A$2:$A$16),ROW($A$2:$A$16)/($A$2:$A$16<>""),$A$2:$A$16)=$D$2,ROW($B$2:$B$16)-ROW($B$2)+1),ROWS(E$2:E2)))
Regards,
A bit longer formula but working, (CSE formula) in E2 and copy down:
=INDEX($B$2:$B$16,SMALL(IF(LOOKUP(ROW($A$2:$A$16),ROW($A$2:$A$16)/($A$2:$A$16<>""),$A$2:$A$16)=$D$2,ROW($B$2:$B$16)-ROW($B$2)+1),ROWS(E$2:E2)))
Regards,
Debraj
Excel Ninja
R.O.T.F.L.S.H.T.I.H.M.H.O.T.W.V.H.T.W.G.C.A.T.H.S.S.A.M.M.R.I.M.R.T.I.W.D.B.I.W.S.L. -
Rolling On The Floor Laughing So Hard That I Hit My Head On The Wall Very Hard That The Wall Got Cracks And The House Started Shaking And My Mom Ran Into My Room Thinking I Was Dead But I Was Still Laughing
Somendra Misra
Excel Ninja
shrivallabha
Excel Ninja
In cell E2 (not at all generic / elegant but applies specifically to your case):
=INDEX($B$2:$B$16,MATCH(LOOKUP(99,$D$2:$D2),$A$2:$A$16,0)+(MOD(ROWS($B$2:B2),5)+5*(MOD(ROWS($B$2:B2),5)=0)-1))
and copy down.
=INDEX($B$2:$B$16,MATCH(LOOKUP(99,$D$2:$D2),$A$2:$A$16,0)+(MOD(ROWS($B$2:B2),5)+5*(MOD(ROWS($B$2:B2),5)=0)-1))
and copy down.
Somendra Misra
Excel Ninja
Merged formula in last file,
In E2,
=INDEX($B$2:$B$16,SMALL(IF(LOOKUP(ROW($A$2:$A$16),ROW($A$2:$A$16)/($A$2:$A$16<>""),$A$2:$A$16)=LOOKUP(99^99,$D$2:D11),ROW($B$2:$B$16)-ROW($B$2)+1),MOD(ROWS(F$2:F11)-1,5)+1))
Regards,
In E2,
=INDEX($B$2:$B$16,SMALL(IF(LOOKUP(ROW($A$2:$A$16),ROW($A$2:$A$16)/($A$2:$A$16<>""),$A$2:$A$16)=LOOKUP(99^99,$D$2:D11),ROW($B$2:$B$16)-ROW($B$2)+1),MOD(ROWS(F$2:F11)-1,5)+1))
Regards,
Debraj
Excel Ninja
Hi Somu!
Apologize, mah fault.. I upload the file first, then made some changes.. so I thought.. you have the Inserted pic Excel file ..
Last night, somehow.. completed the task using OFFSET..
=OFFSET($A$1,MATCH(LOOKUP(2,1/NOT(ISBLANK($D$2:D2)),$D$2:D2),A:A,0)+ROWS(E$2:E2)-LOOKUP(2,1/NOT(ISBLANK($D$2:D2)),ROW($D$2:D2)),1)
You know why..
May be in next version of excel.. Microsoft will continuous blink the green triangle at top left to show.. "Number Stored as text"..
For Forum..
Here is the updated file..
Apologize, mah fault.. I upload the file first, then made some changes.. so I thought.. you have the Inserted pic Excel file ..
Last night, somehow.. completed the task using OFFSET..
=OFFSET($A$1,MATCH(LOOKUP(2,1/NOT(ISBLANK($D$2:D2)),$D$2:D2),A:A,0)+ROWS(E$2:E2)-LOOKUP(2,1/NOT(ISBLANK($D$2:D2)),ROW($D$2:D2)),1)
Thanks Guru.. and apologize to you and Shri too
You know why..
May be in next version of excel.. Microsoft will continuous blink the green triangle at top left to show.. "Number Stored as text"..
For Forum..
Here is the updated file..