Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a Lookup formula ?
Look he
http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "Charlie7805" wrote in message ... |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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a Lookup formula ?
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(indi rect("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 "Niek Otten" wrote in message ... Look he http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "Charlie7805" wrote in message ... |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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a Lookup formula ?
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. -- Biff Microsoft Excel MVP "Tyro" wrote in message t... 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(indi rect("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 "Niek Otten" wrote in message ... Look he http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "Charlie7805" wrote in message ... |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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a Lookup formula ?
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? "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Tyro" wrote in message t... 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(indi rect("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 "Niek Otten" wrote in message ... Look he http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "Charlie7805" wrote in message ... |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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a Lookup formula ?
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/translat...l%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! -- Biff Microsoft Excel MVP "Charlie7805" wrote in message ... 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? "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Tyro" wrote in message t... 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(indi rect("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 "Niek Otten" wrote in message ... Look he http://office.microsoft.com/en-us/ex...260381033.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "Charlie7805" wrote in message ... |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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with lookup formula | Excel Worksheet Functions | |||
Lookup Formula | Excel Discussion (Misc queries) | |||
Lookup formula?? or other | Excel Worksheet Functions | |||
Lookup Formula? | Excel Worksheet Functions | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |