| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup multiple results with gaps
Hi,
My Data is Set-Up in this format. A B D E Hours Dept.Names Criteria Expected Results 8.34 A B 8.34 8.34 7.21 7.64 3.21 7.21 7.21 3.21 6.64 3.21 6.62 8.34 B 7.21 3.21 9.21 C 4.15 8.34 10.11 Column A are the hours. Column B are the Dept. names Cell D2 has the criteria I would like a formula that returns multiple results for Dept. B in cell E2 and filler down. I could use this formula: =INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2))) But if you notice that in column B has gaps between them anyway we could go around that without any helper columns or helper cells? A all in one formula. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup multiple results with gaps
OK, I see what you want.
Try this: (based on your sample data in the range A3:B16): =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATC H(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B $3:B$16,0)+ROWS($1:1)-1),"") Biff "Fin Fang Foom" wrote in message ups.com... Hi, My Data is Set-Up in this format. A B D E Hours Dept.Names Criteria Expected Results 8.34 A B 8.34 8.34 7.21 7.64 3.21 7.21 7.21 3.21 6.64 3.21 6.62 8.34 B 7.21 3.21 9.21 C 4.15 8.34 10.11 Column A are the hours. Column B are the Dept. names Cell D2 has the criteria I would like a formula that returns multiple results for Dept. B in cell E2 and filler down. I could use this formula: =INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2))) But if you notice that in column B has gaps between them anyway we could go around that without any helper columns or helper cells? A all in one formula. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup multiple results with gaps
Biff wrote: OK, I see what you want. Try this: (based on your sample data in the range A3:B16): =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATC H(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B $3:B$16,0)+ROWS($1:1)-1),"") I'm getting a #N/A error. I'm putting the exact formula you gave me. Biff "Fin Fang Foom" wrote in message ups.com... Hi, My Data is Set-Up in this format. A B D E Hours Dept.Names Criteria Expected Results 8.34 A B 8.34 8.34 7.21 7.64 3.21 7.21 7.21 3.21 6.64 3.21 6.62 8.34 B 7.21 3.21 9.21 C 4.15 8.34 10.11 Column A are the hours. Column B are the Dept. names Cell D2 has the criteria I would like a formula that returns multiple results for Dept. B in cell E2 and filler down. I could use this formula: =INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2))) But if you notice that in column B has gaps between them anyway we could go around that without any helper columns or helper cells? A all in one formula. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup multiple results with gaps
I forgot something!
You need a flag to define the end of the range in column B. It'd be easier to show you in a sample file rather than try to explain it. So, here's a sample file: Sample_extract2.xls 14.0kb http://cjoint.com/?lmbGTt680S Biff "Biff" wrote in message ... OK, I see what you want. Try this: (based on your sample data in the range A3:B16): =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATC H(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B $3:B$16,0)+ROWS($1:1)-1),"") Biff "Fin Fang Foom" wrote in message ups.com... Hi, My Data is Set-Up in this format. A B D E Hours Dept.Names Criteria Expected Results 8.34 A B 8.34 8.34 7.21 7.64 3.21 7.21 7.21 3.21 6.64 3.21 6.62 8.34 B 7.21 3.21 9.21 C 4.15 8.34 10.11 Column A are the hours. Column B are the Dept. names Cell D2 has the criteria I would like a formula that returns multiple results for Dept. B in cell E2 and filler down. I could use this formula: =INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2))) But if you notice that in column B has gaps between them anyway we could go around that without any helper columns or helper cells? A all in one formula. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup multiple results with gaps
Ok I got it to work. Thank you very much!
The first try Iit did not work because I use it on another example worksheet I have that instead of texts in column B they were numbers but your formula you provided works well if column B are texts. Can it work both ways for vaules and texts? Biff wrote: OK, I see what you want. Try this: (based on your sample data in the range A3:B16): =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATC H(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B $3:B$16,0)+ROWS($1:1)-1),"") Biff "Fin Fang Foom" wrote in message ups.com... Hi, My Data is Set-Up in this format. A B D E Hours Dept.Names Criteria Expected Results 8.34 A B 8.34 8.34 7.21 7.64 3.21 7.21 7.21 3.21 6.64 3.21 6.62 8.34 B 7.21 3.21 9.21 C 4.15 8.34 10.11 Column A are the hours. Column B are the Dept. names Cell D2 has the criteria I would like a formula that returns multiple results for Dept. B in cell E2 and filler down. I could use this formula: =INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2))) But if you notice that in column B has gaps between them anyway we could go around that without any helper columns or helper cells? A all in one formula. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup multiple results with gaps
Sorry Biff your formula works great.
Thank You very much!!! Fin Fang Foom wrote: Ok I got it to work. Thank you very much! The first try Iit did not work because I use it on another example worksheet I have that instead of texts in column B they were numbers but your formula you provided works well if column B are texts. Can it work both ways for vaules and texts? Biff wrote: OK, I see what you want. Try this: (based on your sample data in the range A3:B16): =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATC H(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B $3:B$16,0)+ROWS($1:1)-1),"") Biff "Fin Fang Foom" wrote in message ups.com... Hi, My Data is Set-Up in this format. A B D E Hours Dept.Names Criteria Expected Results 8.34 A B 8.34 8.34 7.21 7.64 3.21 7.21 7.21 3.21 6.64 3.21 6.62 8.34 B 7.21 3.21 9.21 C 4.15 8.34 10.11 Column A are the hours. Column B are the Dept. names Cell D2 has the criteria I would like a formula that returns multiple results for Dept. B in cell E2 and filler down. I could use this formula: =INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2))) But if you notice that in column B has gaps between them anyway we could go around that without any helper columns or helper cells? A all in one formula. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup multiple results with gaps
You're welcome!
Biff "Fin Fang Foom" wrote in message oups.com... Sorry Biff your formula works great. Thank You very much!!! Fin Fang Foom wrote: Ok I got it to work. Thank you very much! The first try Iit did not work because I use it on another example worksheet I have that instead of texts in column B they were numbers but your formula you provided works well if column B are texts. Can it work both ways for vaules and texts? Biff wrote: OK, I see what you want. Try this: (based on your sample data in the range A3:B16): =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATC H(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B $3:B$16,0)+ROWS($1:1)-1),"") Biff "Fin Fang Foom" wrote in message ups.com... Hi, My Data is Set-Up in this format. A B D E Hours Dept.Names Criteria Expected Results 8.34 A B 8.34 8.34 7.21 7.64 3.21 7.21 7.21 3.21 6.64 3.21 6.62 8.34 B 7.21 3.21 9.21 C 4.15 8.34 10.11 Column A are the hours. Column B are the Dept. names Cell D2 has the criteria I would like a formula that returns multiple results for Dept. B in cell E2 and filler down. I could use this formula: =INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2))) But if you notice that in column B has gaps between them anyway we could go around that without any helper columns or helper cells? A all in one formula. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup multiple results with gaps
Can it work both ways for vaules and texts?
Your posted example was in TEXT so I wrote the formula specifically for TEXT. To make it work for either text or numbers: =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MAT CH(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2, B$3:B$16,0)+ROWS($1:1)-1),"") Change this portion: MATCH("*",B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16,0 )+1),0) To: MATCH(TRUE,B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16, 0)+1)<"",0) This will make the formula an array. So, enter using the key combination of CTRL,SHIFT,ENTER. You still need an end of range flag in column B. Using this modified formula, that flag can be either text or numeric. Biff "Fin Fang Foom" wrote in message ups.com... Ok I got it to work. Thank you very much! The first try Iit did not work because I use it on another example worksheet I have that instead of texts in column B they were numbers but your formula you provided works well if column B are texts. Can it work both ways for vaules and texts? Biff wrote: OK, I see what you want. Try this: (based on your sample data in the range A3:B16): =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATC H(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B $3:B$16,0)+ROWS($1:1)-1),"") Biff "Fin Fang Foom" wrote in message ups.com... Hi, My Data is Set-Up in this format. A B D E Hours Dept.Names Criteria Expected Results 8.34 A B 8.34 8.34 7.21 7.64 3.21 7.21 7.21 3.21 6.64 3.21 6.62 8.34 B 7.21 3.21 9.21 C 4.15 8.34 10.11 Column A are the hours. Column B are the Dept. names Cell D2 has the criteria I would like a formula that returns multiple results for Dept. B in cell E2 and filler down. I could use this formula: =INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2))) But if you notice that in column B has gaps between them anyway we could go around that without any helper columns or helper cells? A all in one formula. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup multiple results with gaps
Hi Biff,
Thank You for the reply. Is there a way to get around the range flag. Because my data fluctuates daily. My data could end at row 50 or row 300 even at row 4000. Can we get around this without using VBA? If not possible thats ok. Biff wrote: Can it work both ways for vaules and texts? Your posted example was in TEXT so I wrote the formula specifically for TEXT. To make it work for either text or numbers: =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MAT CH(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2, B$3:B$16,0)+ROWS($1:1)-1),"") Change this portion: MATCH("*",B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16,0 )+1),0) To: MATCH(TRUE,B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16, 0)+1)<"",0) This will make the formula an array. So, enter using the key combination of CTRL,SHIFT,ENTER. You still need an end of range flag in column B. Using this modified formula, that flag can be either text or numeric. Biff "Fin Fang Foom" wrote in message ups.com... Ok I got it to work. Thank you very much! The first try Iit did not work because I use it on another example worksheet I have that instead of texts in column B they were numbers but your formula you provided works well if column B are texts. Can it work both ways for vaules and texts? Biff wrote: OK, I see what you want. Try this: (based on your sample data in the range A3:B16): =IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATC H(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B $3:B$16,0)+ROWS($1:1)-1),"") Biff "Fin Fang Foom" wrote in message ups.com... Hi, My Data is Set-Up in this format. A B D E Hours Dept.Names Criteria Expected Results 8.34 A B 8.34 8.34 7.21 7.64 3.21 7.21 7.21 3.21 6.64 3.21 6.62 8.34 B 7.21 3.21 9.21 C 4.15 8.34 10.11 Column A are the hours. Column B are the Dept. names Cell D2 has the criteria I would like a formula that returns multiple results for Dept. B in cell E2 and filler down. I could use this formula: =INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15 )-ROW(A$2)+1,""),ROWS(A$2*:A2))) But if you notice that in column B has gaps between them anyway we could go around that without any helper columns or helper cells? A all in one formula. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup formulas to obtain multiple results | Excel Worksheet Functions | |||
| Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
| Calculate multiple results from multiple input values? | Excel Discussion (Misc queries) | |||
| Multiple lookup value's | Excel Worksheet Functions | |||
| Multiple Criteria Lookup Question | Excel Discussion (Misc queries) | |||
Linear Mode
