Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to look up a value in a list and return multiple correspondingvalues
Hi Focus,
I read those post and i have almost the same problems and try to use MVP to lookup my values. So let me explain what I need. I have 10 worksheets with the same "face", that means all worksheet are equal filled with names, numbers and dates (10 coluns). In particular I have 2 coluns with dates and if one of those two is blak, so I need to copy entire row to other sheet for resume report. Until now I try to use Vlookup formulas to find values in auxiliary column that compare those 2 dates cells and print "OK" (if those tow are not blank) or "Not ok" if the second cell of dates is blank. So in the resume report worksheet I try to use index, small functions, but My office is 2007 in portuguese language and (=SE(ISERROR(índice($A $1:$B$51,Menor(SE($A$1:$A$51="Sem Resposta",linha($A$1:$A$51)),) (ou =INDEX($A$2:$B$99,SMALL(IF($A$2:$A$99=$A$103,ROW($ A$2:$A $99)),ROW(1:1)),2) ) those not work neven I translate the funcitons. My problem is (How to look up a value in a list and return multiple corresponding values) in excel 2007 because if I use the auxiliary if function on other colun to find "OK" or "not OK" values the Vlookup stop looking when find the first "not ok value", so in this point I need a kind of INDEX function but does not work on excel 2007. If those MVP can solve my problem, help me how!? Or if has a soluction with functions only, that good to. If any one has a suggestion, pls write. Thanks Felipe Resuming I need to find in various sheets where the "date cells" are blaks and copy entire row in "resume report worksheet" file. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to look up a value in a list and return multiple corresponding values
G'day Felipe
Visit Ron DeBruins Website He has some great codes there that might help you with what you are trying to achieve. http://www.rondebruin.nl/tips.htm HTH Mark. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to look up a value in a list and return multiple correspon
Not sure if this works in Excel 2007 or not, but try anyway:
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"") Cell E1 contains a value that has a few matches in range B1:B20 Regards Ryan--- -- RyGuy "NoodNutt" wrote: G'day Felipe Visit Ron DeBruins Website He has some great codes there that might help you with what you are trying to achieve. http://www.rondebruin.nl/tips.htm HTH Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return multiple values in a list? | Excel Worksheet Functions | |||
How to look up a value in a list and return multiple corresponding | Excel Discussion (Misc queries) | |||
How to look up a value in list & return multiple answers | Excel Discussion (Misc queries) | |||
How to look up a value in a list and return multiple corresponding | Excel Discussion (Misc queries) | |||
How to look up a value in list & return multiple corresponding val | Excel Discussion (Misc queries) |