Hi, everyone!

I use Vlookup for searching a cell value, but the function always shows only
the first result, that found. I would need the other values too, that matches
my criteria. How can i see all the results, not only the first?

Hi sheva

See <Arbitrary Lookups
http://www.cpearson.com/excel/lookups.htm#DoubleLookup

But maybe you can use DataAutoFilter ?



--
Regards Ron de Bruin
http://www.rondebruin.nl


"sheva" wrote in message ...
Hi, everyone!

I use Vlookup for searching a cell value, but the function always shows only
the first result, that found. I would need the other values too, that matches
my criteria. How can i see all the results, not only the first?

sheva wrote:
Hi, everyone!

I use Vlookup for searching a cell value, but the function always shows only
the first result, that found. I would need the other values too, that matches
my criteria. How can i see all the results, not only the first?

With your data in a range named "Tbl", the lookup value in a cell named
"luVal" and the number of the column in the table from which the sought
value is to be returned in a cell named "luCol", then using only
built-in functions, enter and fill down

=IF(ROWS(A$1:A1)<=COUNTIF(INDEX(Tbl,0,1),luVal),IN DEX(INDEX(Tbl,0,luCol),SMALL(IF(INDEX(Tbl,0,1)=luV al,ROW(INDIRECT("A1:A"&ROWS(Tbl)))),ROWS(A$1:A1))) ,"")


or if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, enter and
fill down

=IF(ISERROR(INDEX(vlookups(luVal,Tbl,luCol),ROW(A1 ))),"",INDEX(vlookups(luVal,Tbl,luCol),ROW(A1)))

Alan Beban