Prev: SUM
Next: Finding NEXT to last, non zero numeric value in a row
From: Jafferi on 15 Apr 2010 23:49
In Sheet a, i have these info

Employee Number hiring date
122555 apr 5, 2010
152666 apr 4, 2010
123554 apr 4, 2010
451225 apr 5, 2010

In Sheet b, i need this info (that pulls from Sheet a)

Employee Number hiring date
122555 apr 5, 2010
451225 apr 5, 2010

How can I do a formula in Sheet b that will pull all the data from sheet a
for a specific date e.g. apr 5, 2010.
From: L. Howard Kittle on 16 Apr 2010 00:17
Try VLOOKUP with a formula that looks like this in sheet B.

=VLOOKUP(F1,A!A1:B4,2,0)

Where F1 is the lookup value on sheet B and A!A1:B4 is the table_array on
sheet A.

HTH
Regards,
Howard


"Jafferi" <j134842(a)gmail.com> wrote in message
news:CD610F36-C6F4-4F59-867E-989FBA81CC82(a)microsoft.com...
> In Sheet a, i have these info
>
> Employee Number hiring date
> 122555 apr 5, 2010
> 152666 apr 4, 2010
> 123554 apr 4, 2010
> 451225 apr 5, 2010
>
> In Sheet b, i need this info (that pulls from Sheet a)
>
> Employee Number hiring date
> 122555 apr 5, 2010
> 451225 apr 5, 2010
>
> How can I do a formula in Sheet b that will pull all the data from sheet a
> for a specific date e.g. apr 5, 2010.


From: ozgrid.com on 16 Apr 2010 00:15
VLOOKUP
http://www.ozgrid.com/Excel/excel-vlookup-formula.htm
Or INDEX/MATCH
http://www.ozgrid.com/Excel/left-lookup.htm




--
Regards
Dave Hawley
www.ozgrid.com



"Jafferi" <j134842(a)gmail.com> wrote in message
news:CD610F36-C6F4-4F59-867E-989FBA81CC82(a)microsoft.com...
> In Sheet a, i have these info
>
> Employee Number hiring date
> 122555 apr 5, 2010
> 152666 apr 4, 2010
> 123554 apr 4, 2010
> 451225 apr 5, 2010
>
> In Sheet b, i need this info (that pulls from Sheet a)
>
> Employee Number hiring date
> 122555 apr 5, 2010
> 451225 apr 5, 2010
>
> How can I do a formula in Sheet b that will pull all the data from sheet a
> for a specific date e.g. apr 5, 2010.

From: Jacob Skaria on 16 Apr 2010 01:19
Hi Jafferi

With the query date in Sheet2 cell C1 apply the below formula in cell A2 and
copy down/across as required. Please note that this is an array formula. You
create array formulas in the same way that you create other formulas, except
you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula
Bar' you can notice the curly braces at both ends like "{=<formula>}"

=IF(COUNTIF(Sheet1!$B$1:$B$1000,$C$1)<ROW($A1),"",
INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$1000=$C$1,
ROW($A$1:$A$1000)),ROW($A1))))


The result would look like for the query date in cell C1. Dont miss to
format column B to a date format of your choice

Col A Col B Col C
EmpNum HireDate 4-Apr-10
152666 4-Apr-10
123554 4-Apr-10


--
Jacob (MVP - Excel)


"Jafferi" wrote:

> In Sheet a, i have these info
>
> Employee Number hiring date
> 122555 apr 5, 2010
> 152666 apr 4, 2010
> 123554 apr 4, 2010
> 451225 apr 5, 2010
>
> In Sheet b, i need this info (that pulls from Sheet a)
>
> Employee Number hiring date
> 122555 apr 5, 2010
> 451225 apr 5, 2010
>
> How can I do a formula in Sheet b that will pull all the data from sheet a
> for a specific date e.g. apr 5, 2010.
 | 
Pages: 1
Prev: SUM
Next: Finding NEXT to last, non zero numeric value in a row