LinkBack URL
About LinkBacks
Hi team,
Need help to produce a formula which is mentioned in the attached file.
Formula to auto populate
Hi team,
Need help to produce a formula which is mentioned in the attached file.
In C8, an array formula, copied across and down:
=IFERROR(INDEX(Data!H:H,SMALL(IF(Data!$B$3:$B$28=Summary!$A$8,ROW(Data!$B$3:$B$28)),ROWS(C$8:C8))),"")
and iun B19, copied across.
Similarly for the lower replicate set.
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Can also try below without array formula, and copy across the cells:
C8=IFERROR(INDEX(Data!H$3:H$28,MATCH(LARGE(INDEX((Data!$B$3:$B$28=$A$8)/ROW(Data!$B$3:$B$28),0),IF(ROWS($A$8:$A8)>COUNTIF(Data!$B$3:$B$28,$A$8),-1,ROWS($A$8:$A8))),INDEX((Data!$B$3:$B$28=$A$8)/ROW(Data!$B$3:$B$28),0),0)),"")
Caveat: if your source data are blank cells (e.g. ROUTE and ROUTE2) you will end up getting 0, quick workaround would be formatting your blank cells to TEXT and insert a SPACE
_______________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved
Alvin
Superb..!
Thank you very much Glenn & Alvin.
Thanks for the rep. If you DO have blank cells that give zeros, can I suggest that you DO NOT follow Alvin's advice, but come back and ask again...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks