I have many lines of data, from which I want to look up and return a value
for two values. For example, I want to see a product price for a specific
customer.
The data may change with lines being inserted or delated, so rather than
specifying the range I would like to look for two specific values, is this
possible?

Say you have customers in column A and products in column B and prices in
column C:


=SUMPRODUCT(--(A1:A100="Fred"),--(B1:B100="dogfood"),--(C1:C100))
--
Gary's Student
gsnu200711


"Moncrieff" wrote:

I have many lines of data, from which I want to look up and return a value
for two values. For example, I want to see a product price for a specific
customer.
The data may change with lines being inserted or delated, so rather than
specifying the range I would like to look for two specific values, is this
possible?

I think what you want is a SUMPRODUCT() formula. If I understand correctly,
you may have a single product that you have priced differently to various
customers, so you want to know how much to sell a Widget to Jone's Widget
Outlet as opposed to how much you sell one to Smith's Widget Shop, right?

As long as the pairing of widget part numbers and customer idenfiers is
unique, this should work; Product ID in A, Customer ID in B, price in C and
you have entries from rows 2 through 10005

=SUMPRODUCT(--($A$2:$A$10005="WidgetType#4"),--($B$2:$B$10005="Jone's Widget
Outlet"),($C$2:$C$10005))

You could make the formula flexible by picking up the product # and customer
ID from cells where you might choose them from a list, so the formula could
look like this:
=SUMPRODUCT(--($A$2:$A$10005=V2),--($B$2:$B$10005=W2),($C$2:$C$10005))

For more flexibility in the range referenced, look into giving the columns
of information Names then Excel will make the addressing dynamic,
automatically adjusting it internally as new rows are added or old ones
deleted. Look up Named Ranges in Help. But as a start to that, what you do
(based on the above example) is start by selecting A2:A10005 and then typing
a name in the Name Box (where you see the address of the cell you have chosen
just above the A column identifier). Type in something like ProductID and
press the [Enter] key - you must press the enter key or Excel will not
remember the name. Then choose B2:B10005 and give it a name like
CustomerList and finally C2:C10005 and maybe a name like CustomPrice and your
formula could become:

=SUMPRODUCT(--(ProductID=V2),--(CustomerList=W2),(CustomPrice))

Play around a little with inserts and deletes in such ranges to see how
Excel keeps up with them. It can fool you sometimes, like if you insert at
the very first row of a named range, the insertion ends up outside of the
named range and simply adding to the bottom of the list doesn't include it in
the range.
For your use it wouldn't hurt to have a couple of rows with no information
in them, so when this is the case I often recommend to people just getting
used to lists to include an extra row below the end of the list and to shade
the first cell in the list and the last one. So you could choose A2:A10006
(same for A and C) and then shade cells A/B/C2 and A/B/C10006 and then I tell
them to always "color inside the lines" - that is, to always insert/delete
rows between the shaded cells to make sure they are working with the list and
not outside of it.



"Moncrieff" wrote:

I have many lines of data, from which I want to look up and return a value
for two values. For example, I want to see a product price for a specific
customer.
The data may change with lines being inserted or delated, so rather than
specifying the range I would like to look for two specific values, is this
possible?

Fantastic - absolutely perfect! This will save me time.

Many thanks

"JLatham" wrote:

I think what you want is a SUMPRODUCT() formula. If I understand correctly,
you may have a single product that you have priced differently to various
customers, so you want to know how much to sell a Widget to Jone's Widget
Outlet as opposed to how much you sell one to Smith's Widget Shop, right?

As long as the pairing of widget part numbers and customer idenfiers is
unique, this should work; Product ID in A, Customer ID in B, price in C and
you have entries from rows 2 through 10005

=SUMPRODUCT(--($A$2:$A$10005="WidgetType#4"),--($B$2:$B$10005="Jone's Widget
Outlet"),($C$2:$C$10005))

You could make the formula flexible by picking up the product # and customer
ID from cells where you might choose them from a list, so the formula could
look like this:
=SUMPRODUCT(--($A$2:$A$10005=V2),--($B$2:$B$10005=W2),($C$2:$C$10005))

For more flexibility in the range referenced, look into giving the columns
of information Names then Excel will make the addressing dynamic,
automatically adjusting it internally as new rows are added or old ones
deleted. Look up Named Ranges in Help. But as a start to that, what you do
(based on the above example) is start by selecting A2:A10005 and then typing
a name in the Name Box (where you see the address of the cell you have chosen
just above the A column identifier). Type in something like ProductID and
press the [Enter] key - you must press the enter key or Excel will not
remember the name. Then choose B2:B10005 and give it a name like
CustomerList and finally C2:C10005 and maybe a name like CustomPrice and your
formula could become:

=SUMPRODUCT(--(ProductID=V2),--(CustomerList=W2),(CustomPrice))

Play around a little with inserts and deletes in such ranges to see how
Excel keeps up with them. It can fool you sometimes, like if you insert at
the very first row of a named range, the insertion ends up outside of the
named range and simply adding to the bottom of the list doesn't include it in
the range.
For your use it wouldn't hurt to have a couple of rows with no information
in them, so when this is the case I often recommend to people just getting
used to lists to include an extra row below the end of the list and to shade
the first cell in the list and the last one. So you could choose A2:A10006
(same for A and C) and then shade cells A/B/C2 and A/B/C10006 and then I tell
them to always "color inside the lines" - that is, to always insert/delete
rows between the shaded cells to make sure they are working with the list and
not outside of it.



"Moncrieff" wrote:

I have many lines of data, from which I want to look up and return a value
for two values. For example, I want to see a product price for a specific
customer.
The data may change with lines being inserted or delated, so rather than
specifying the range I would like to look for two specific values, is this
possible?

Thanks for the feedback, glad to have helped. I just wonder why Gary"s post
wasn't already up when I put mine in the queue - he's offered the same
solution.

"Moncrieff" wrote:

Fantastic - absolutely perfect! This will save me time.

Many thanks

"JLatham" wrote:

I think what you want is a SUMPRODUCT() formula. If I understand correctly,
you may have a single product that you have priced differently to various
customers, so you want to know how much to sell a Widget to Jone's Widget
Outlet as opposed to how much you sell one to Smith's Widget Shop, right?

As long as the pairing of widget part numbers and customer idenfiers is
unique, this should work; Product ID in A, Customer ID in B, price in C and
you have entries from rows 2 through 10005

=SUMPRODUCT(--($A$2:$A$10005="WidgetType#4"),--($B$2:$B$10005="Jone's Widget
Outlet"),($C$2:$C$10005))

You could make the formula flexible by picking up the product # and customer
ID from cells where you might choose them from a list, so the formula could
look like this:
=SUMPRODUCT(--($A$2:$A$10005=V2),--($B$2:$B$10005=W2),($C$2:$C$10005))

For more flexibility in the range referenced, look into giving the columns
of information Names then Excel will make the addressing dynamic,
automatically adjusting it internally as new rows are added or old ones
deleted. Look up Named Ranges in Help. But as a start to that, what you do
(based on the above example) is start by selecting A2:A10005 and then typing
a name in the Name Box (where you see the address of the cell you have chosen
just above the A column identifier). Type in something like ProductID and
press the [Enter] key - you must press the enter key or Excel will not
remember the name. Then choose B2:B10005 and give it a name like
CustomerList and finally C2:C10005 and maybe a name like CustomPrice and your
formula could become:

=SUMPRODUCT(--(ProductID=V2),--(CustomerList=W2),(CustomPrice))

Play around a little with inserts and deletes in such ranges to see how
Excel keeps up with them. It can fool you sometimes, like if you insert at
the very first row of a named range, the insertion ends up outside of the
named range and simply adding to the bottom of the list doesn't include it in
the range.
For your use it wouldn't hurt to have a couple of rows with no information
in them, so when this is the case I often recommend to people just getting
used to lists to include an extra row below the end of the list and to shade
the first cell in the list and the last one. So you could choose A2:A10006
(same for A and C) and then shade cells A/B/C2 and A/B/C10006 and then I tell
them to always "color inside the lines" - that is, to always insert/delete
rows between the shaded cells to make sure they are working with the list and
not outside of it.



"Moncrieff" wrote:

I have many lines of data, from which I want to look up and return a value
for two values. For example, I want to see a product price for a specific
customer.
The data may change with lines being inserted or delated, so rather than
specifying the range I would like to look for two specific values, is this
possible?