Re: Looking up a value in a different file

Domenic

unread,

Jun 11, 2005, 12:45:26 PM6/11/05

to

Assumptions:

1) Workbook1.xls Sheet1 contains your pricing, and that Column A
contains the book name and Column B contains the price.

2) Workbook2.xls Sheet1 contains your orders, and that Column A contains
the book and Column B the quantity.

Formula:

[Workbook2.xls]Sheet1!C1, copied down:

=B1*SUMPRODUCT(--('[Workbook1.xls]Sheet1'!$A$1:$A$100=A1),--('[Workbook1.
xls]Sheet1'!$B$1:$B$100))

Hope this helps!

In article <37F6C006-411A-4827...@microsoft.com>,
"Watercolor artist" <Watercolor art...@discussions.microsoft.com>
wrote:

> I have a column in one spreadsheet (Call it "Orders") into which I enter a
> book name that I sell, a second column into which I enter a quantity, and a
> third column into which the price is entered by looking up the book and
> quantity in a different spreadsheet (call it "Pricing") in a different file,
> finding the book's price, multiplying the quantity by the price, and
> inserting that into the cell containing the lookup function. I don't know how
> to write that formula and would appreciate help.

CLR

unread,

Jun 11, 2005, 12:50:25 PM6/11/05

to

In C2 maybe something like this...........

=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:B,2,FALSE)*B2

You may have to adjust the Path, and SheetName and Range accordingly
Vaya con Dios,
Chuck, CABGx3

"Watercolor artist" <Watercolor art...@discussions.microsoft.com> wrote in
message news:37F6C006-411A-4827...@microsoft.com...

artist@discussions.microsoft.com Watercolor artist

unread,

Jun 12, 2005, 1:05:02 PM6/12/05

to

Muchas gracias, Chuck. Your solution worked perfectly.

Howard

Watercolor artist

unread,

Jun 12, 2005, 1:06:03 PM6/12/05

to

Domenic,

Thanks for trying to help. I couldn't get your formula to work in my
situation, but I was able to adapt Chuck's.

Howard

Watercolor artist

unread,

Jun 13, 2005, 10:37:16 AM6/13/05

to

Chuck,

What if the lookup price was in Column C rather than B and that I wanted to
ignore what's in B?

Also, in "A:B,2,FALSE," what exactly do those parameters mean?

Howard

Domenic

unread,

Jun 13, 2005, 12:16:42 PM6/13/05

to

In article <A5826360-0BF7-447E...@microsoft.com>,
"Watercolor artist" <Watercol...@discussions.microsoft.com>
wrote:

> What if the lookup price was in Column C rather than B and that I wanted to
> ignore what's in B?

=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:C,3,FALSE)*B2

> Also, in "A:B,2,FALSE," what exactly do those parameters mean?

Have a look at the help menu for a detailed explanation.

Hope this helps!

CLR

unread,

Jun 13, 2005, 4:26:11 PM6/13/05

to

Then use..........
=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:C,3,FALSE)*B2

A:C means for Excel to look up A2 in the Range of column A to column C,
(actually only lookup in column A, but capturing columns B and C for data
retrieval).........this can be limited if you wish to something like
A1:C100, ...............the "3", means that when Excel finds a matching
Value in column A, to step over to the right 3 columns and return the value
in that cell...............FALSE means to provide a return only if an exact
match is found, otherwise an error message.

hth

Vaya con Dios,
Chuck, CABGx3

"Watercolor artist" <Watercol...@discussions.microsoft.com> wrote in
message news:A5826360-0BF7-447E...@microsoft.com...