sumproduct formula

[Todd]

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

 

[Toppers]

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!

 

[Toppers]

Sorry ... mental aberration ....

try


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

 

[Todd]

Thanks for the help! I am getting a NA error message. I don't see any
reason why but I have never done any multiple matches before. I made sure to
remove any hidden text and retyped the data but I keep getting the same
error. What can I do?

Thanks again,



Todd

 

[Peo Sjoblom]

Enter it with ctrl + shift & enter

 

[Toppers]

NA usually indicates something doesn't match. Are you comparing like with
like e.g are BOTH the item numbers number OR text?

A small test I did worked OK.

If you want send s/sheet to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

HTH

 

[Todd]

Thanks Toppers. I did a seperate test and the formula worked great. But not
in that workbook. I am looking at cell formats but do not see anything. So
I sent a sample. Thanks very much.


Todd

 

[Toppers]

Nothing received as yet ......13:17 PST.

Thanks Toppers. I did a seperate test and the formula worked great. But not
in that workbook. I am looking at cell formats but do not see anything. So
I sent a sample. Thanks very much.


Todd