Vlookup across worksheets.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tia - 17 Sep 2007 21:08 GMT

I have the following worksheets:

Reconciliation
Allred, B.
Hills, C.

On the Reconciliation, I want to pull information from the other two
worksheets. In cell A2, I've entered the date. I want to use a VLOOKUP to
pull information from the other 2 worksheets that coordinate with the date in
A2.

I found an example of what I thought I wanted my formula to look like but it
just puts a 0 in the destination cell (B4).

=VLOOKUP(A2,Reconciliation!A$1:F$100,3,0)

If I have a match on the date of column A on Allred, B. table to cell A2 on
the Reconicilation table, than I want the data from Column B (Allred, B.
table) that corresponds to the date, entered on the Reconciliation Table Cell
B4.

Thanks for your time!

Reply to this Message

Bernard Liengme - 17 Sep 2007 21:42 GMT

I am a little confused.
Is this formula is on the Reconciliation sheet:
=VLOOKUP(A2,Reconciliation!A$1:F$100,3,0) ?

If you want to pull data from the other sheet, should it not be
=VLOOKUP(A2,'Allred, B'!A$1:F$100,3,0)

The 3 indicates the third column of A:F so this is column C.
This will pull from a row in C1:C100 into the cell containing the formula.

Please come back with a clarification if I am totally screwed up!
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

>I have the following worksheets:
>
[quoted text clipped - 23 lines]
>
> Thanks for your time!

Reply to this Message

Tia - 17 Sep 2007 22:34 GMT

That works great!!

Is there a limit to the amount of columns you can have Vlookup look at? The
formula works great up to 12, then I get a REF error on 13-my formula looks
like the following: =VLOOKUP(A2, 'Allred'!A$1:L$100,13,0).

> I am a little confused.
> Is this formula is on the Reconciliation sheet:
[quoted text clipped - 36 lines]
> >
> > Thanks for your time!

Reply to this Message

Peo Sjoblom - 17 Sep 2007 22:39 GMT

It's because the lookup table has less columns than what you are indexing.
Your table is A1:L100 and if you count from A to (including) L that is only
12 columns so you tell Excel to return values from the 13th column in a
table that has only 12 columns. If you would change the lookup table to
A1:M100 it would not return a ref error

Signature

Regards,

Peo Sjoblom

> That works great!!
>
[quoted text clipped - 51 lines]
>> >
>> > Thanks for your time!

Reply to this Message