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

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.
 
R

ryguy7272

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---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
How to look up a value in a list and return multiple corresponding values 7
how to lookup a value in a list and return multiple corresponding 2
How to look up a value? 9
Index return value REF#! 2
need help on either doing a loop and counting specific values orusing subtotal function 2
Return value from a Two Variable Data Table with Dates 1
With F1 value, look at A, if match or partial match return B 5

Top