View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Toppers Toppers is offline
external usenet poster
 
Posts: 4,339
Default sumproduct formula

In your example, how do we know if unit of measure is "Yard" or "Box" for
item=10169 and Base=No. Or is this typo?

You probly need INDEX/Match Solution

=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Base"),0))

Enter with Ctrl+Shift+Enter


Should "Base" not be "Yes" or "No"?

I'm confused!

"Todd" wrote:

Hi,

Help please? I need to match an item number on one page to an item number
on a second page and then select the base unit of measure. I have a table
that has item numbers and prices depending on the unit of measure. I want to
show the base unit of measure, in text, on the first page. But I can't
figure out how to do that.

I am trying to use a sumproduct formula to return text. And it isn't
working. Thanks,

Todd

=SUMPRODUCT(--('Price List'!$A$2:$A$500=A5),--('Price
List'!$C$2:$C$500="Base"),TEXT('Price List'!$D$2:$D$500,0))

Price List
Item Number Base Unit of Measure
10169 yes pound
10169 no yard
10169 no box