copy formula

[Jafferi]

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.

 

[ozgrid.com]

VLOOKUP
http://www.ozgrid.com/Excel/excel-vlookup-formula.htm
Or INDEX/MATCH
http://www.ozgrid.com/Excel/left-lookup.htm

 

[L. Howard Kittle]

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

 

[Jacob Skaria]

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