How to look up a value in a list and return multiple correspondingvalues

  • Thread starter Thread starter felipe.junqueira
  • Start date Start date
F

felipe.junqueira

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.
 
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---
 
Back
Top