 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Help with a Lookup formula ?
I have tabs in a workbook for every year, 2003 to 2007. Each sheet is a list of employees who have had an <event> during the year. Some have 2 or more <events> in a single year. I want to add a new sheet with a master list of all employees and insert a lookup formula alongside each one to search each sheet returning every <event> and the date associated with it. A vlookup will only return one event per employee, per sheet. It will not find multiple <events>??? Need some help please. Thanks. Look here: http://office.microsoft.com/en-us/excel/HA012260381033.aspx  SignatureKind regards, Niek Otten Microsoft MVP - Excel |I have tabs in a workbook for every year, 2003 to 2007. Each sheet is a list | of employees who have had an <event> during the year. Some have 2 or more [quoted text clipped - 10 lines] | | Thanks. The formula at the MS site is wrong It is: =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)) When entered as an array formula in d1:d7, it always returns 1. The formula should be =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:7)) However, that formula ceases to work properly if a row is inserted before row 1. The formula should actually be: =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(indirect("1:7"))) and the final formula also does not work if rows are inserted before row 1. The final formula is which does not work without the first correction is: =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2) To work properly the final formula should be: =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)-ROW($A$1:$A$1)+1),ROW(INDIRECT("1:7"))),2) That formula returns the 3 values for Ashish - 234, 534 and 834 in D1, D2, D3 and #NUM errors in D4, D5, D6, D7 and allows for insertion of rows before row 1. The original formula with the correction for ROW(1:1) which is ROW(1:7) returns identical results if there are no rows inserted before row 1. Tyro > Look here: > [quoted text clipped - 18 lines] > | > | Thanks. >When entered as an array formula in d1:d7, it always returns 1. That formula was not written as a range array. It's supposed to be entered in a single cell then copied down. To make it robust against row insertions above the range: =INDEX(B$1:B$7,SMALL(IF(A$1:A$7=A$10,ROW(B$1:B$7)-MIN(ROW(B$1:B$7))+1),ROWS(B$10:B10))) To include an efficient error trap: =IF(ROWS(B$10:B10)<=COUNTIF(A$1:A$7,A$10),INDEX(B$1:B$7,SMALL(IF(A$1:A$7=A$10,ROW(B$1:B$7)-MIN(ROW(B$1:B$7))+1),ROWS(A$10:A10))),"") If you're using Excel 2007 you could use IFERROR but for this particular formula IFERROR *isn't* more efficient than ROWS(B$10:B10)<=COUNTIF(A$1:A$7,A$10) when an error is anticipated. However, when there is no error condition then IFERROR *is* slightly more efficient. SignatureBiff Microsoft Excel MVP > The formula at the MS site is wrong It is: > =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)) When entered as an [quoted text clipped - 41 lines] >> | >> | Thanks. Thank you for the below, but I'm struggling with it. I copied the data as shown at the MS site and entered your corrected formula FAR below but keep getting a #VALUE! ERROR. What am I missing? > >When entered as an array formula in d1:d7, it always returns 1. > [quoted text clipped - 61 lines] > >> | > >> | Thanks. Well, at this point there's not much I can suggest. If you want to upload a sample file that demonstrates what you're trying to do then someone (me!) may take a look at it and figure it out. I use this free file hosting site when I post sample files: http://translate.google.com/translate?hl=en&sl=fr&u=http://cjoint.com/&sa=X&oi=t ranslate&resnum=1&ct=result&prev=/search%3Fq%3Dcjoint%26hl%3Den%26lr%3D It's a French site translated to English. Upload your file then post back with the link to the file. Note the size limits! SignatureBiff Microsoft Excel MVP > Thank you for the below, but I'm struggling with it. I copied the data as > shown at the MS site and entered your corrected formula FAR below but keep [quoted text clipped - 77 lines] >> >> | >> >> | Thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.