How can Vlookup help in that??

[dalipsinghbisht]

What should i do when i have to work in an Excel File and there are man
Account no in Sheet 1 and Sheet 2 contains many account no and ther
Date now i have to take Account no. from sheet 1 one by one and come t
the Sheet 2 to search there availability and paste there Date in Shee
1.

i.e.

sheet 1 contains a column
Coulmn A
Account No.
0004317575603512196
0004006676010767385
0004317575603323636
0004317575603277113
0004317575603539660.

Sheet 2

Coulmn A
Account No.
0004317575603631103
0004317575603578569
0004317575603097701
0004317575603512196
0004006661130015717
0004006676010767385
0004317575603548794
0004317575603323636
0004006661030774793
0004317575603277113
0004317575603539660

Sheet 2

Coulmn B
Date
01/30/06
01/30/06
01/30/06
01/30/06
01/27/06
01/30/06
01/05/06
01/31/06
01/18/06




Is there any way that we can paste all 5 Account No's Date to the fron
of Account Nos in Sheet 1

Thanks in advanc

 

[Ardus Petus]

In sheet1 column B2, type following formula, then drag down:
=VLOOKUP(A2,Sheet2!A2:B13,2,0)

Adjust search range to your needs

HTH
--
AP

"dalipsinghbisht"
<[email protected]> a écrit dans
le message de
news:[email protected]...

 

[dalipsinghbisht]

That is not working properly cuz some of the date are not coming in tha
fourmula..

Please hel

 

[Ardus Petus]

Do you mean SOMe of the dates from Sheet2 appear and SOME don't ?!?!

--
AP

"dalipsinghbisht"
<[email protected]> a écrit dans
le message de
news:[email protected]...

 

[dalipsinghbisht]

Yes you are right Some dates appears and some don'ts.... please take
look... here is the link for the file....


http://www.geocities.com/dalipsinghbisht/tryit.zip



Thank

 

[dalipsinghbisht]

When i apply a vlookup formula for matching Account no. there is mor
Account no. but less Date with your Fourmula..

 

[Ardus Petus]

I'm baffled...

--
AP

"dalipsinghbisht"
<[email protected]> a écrit dans
le message de
news:[email protected]...

 

[Guest]

This works on your w/book:

<Accounts> is the named range for data on Sheet2 i.e A2: B8439

More than half the accounts don't have corresponding dates (which in your
original posting you said were Amounts!) and are left blank.

=IF(ISERROR(VLOOKUP(A2,Accounts,2,FALSE))," ",(VLOOKUP(A2,Accounts,2,FALSE)))

I'm baffled...

--
AP

"dalipsinghbisht"
<[email protected]> a écrit dans
le message de

 

[Pete_UK]

Try this formula in cell B2 of your Sheet1:

=IF(ISNA(VLOOKUP(A2,Sheet2!A$2:B$8439,2,0)),"n/p",VLOOKUP(A2,Sheet2!A$2:B$8439,2,0))

Format the cell as date then copy down to the bottom of your data, i.e.
to row 11797. "n/p" stands for not present.

Hope this helps.

Pete