VLOOKUP show all results

N

New_Man

hi people i am working on a database. i want to use the vlookup for example..

Names age Vlookup john age
john 1 1
bill 2 4
pat 3 5
john 4 7
john 5
bill 6
john 7

I want all the john age to be displayed, my database is over a thousand
rows, so i was wonder if i can just select the whole entire row like B:B and
it find all the ages for john only.. help plz and Thank you for your time ....
 
C

CLR

Maybe the Autofilter will do what you want......Data > Filter > AutoFilter,
then in the dropdown arrow for the name column choose John.....it will
filter and show only the John rows........if you want then click on the 4 in
the age dropdown arrow and it will filter to only the 4yo Johns, etc
etc..........then do Data > Filter > AutoFilter again to return everything
to normal.....

Vaya con Dios,
Chuck, CABGx3


New_Man said:
hi people i am working on a database. i want to use the vlookup for example..

Names age Vlookup john age
john 1 1
bill 2 4
pat 3 5
john 4 7
john 5
bill 6
john 7

I want all the john age to be displayed, my database is over a thousand
rows, so i was wonder if i can just select the whole entire row like B:B and
it find all the ages for john only.. help plz and Thank you for your time
Click to expand...
.....
 
R

Ragdyer

Say the names are in Column A, from A1 to A1000.

Ages from B1 to B1000.

Enter the name you're looking to find into C1,
And enter this *array* formula in D1:

=IF(COUNTIF($A$1:$A$1000,$C$1)>=ROWS($1:1),INDEX($B$1:$B$1000,SMALL(IF($A$1:
$A$1000=$C$1,ROW($1:$1000)),ROW(1:1))),"")

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Now, copy this down enough rows so that you're sure that you have all the
possibilities returned.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
New_Man said:
hi people i am working on a database. i want to use the vlookup for example..

Names age Vlookup john age
john 1 1
bill 2 4
pat 3 5
john 4 7
john 5
bill 6
john 7

I want all the john age to be displayed, my database is over a thousand
rows, so i was wonder if i can just select the whole entire row like B:B and
it find all the ages for john only.. help plz and Thank you for your time
Click to expand...
.....
 
H

Hans Knudsen

Nice formula!
Can you tell me why I can't use Excel's Formula Evaluator here, that is the first four steps work fine but on the fifth step I get
the message: Microsoft Excel has encountered a problem and needs to close.
I use Excel XP.

Hans Knudsen
 
B

Biff

Hi!

That happens to me *ALL THE TIME* when using the formula evaluator to step
through certain types of array formulas.

An alternative is to highlight the parts of the formula in the formula bar
and then press F9. This doesn't show the sequence of steps that the formula
evaluator does but it will show the calculated expressions.

Biff
 
Top