VLOOKUP show all results
 

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

VLOOKUP show all results
 

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" wrote in message
...
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
.....

VLOOKUP show all results
 

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" wrote in message
...
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
.....

VLOOKUP show all results
 

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



"Ragdyer" skrev i en meddelelse ...
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" wrote in message
...
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
....

VLOOKUP show all results
 

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

"Hans Knudsen" wrote in message
...
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



"Ragdyer" skrev i en meddelelse
...
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" wrote in message
...
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
....