If Then Lookups - If Someone helps me, Then I will be happier!!!!

[Andy]

Okay I'm really stumped here but I know I shouldn't be.

I'm trying to return a value from a table. The table is sortof multi-use.
I've had a similiar formula that did a SumIf from the same table, but now I
just want to return the value of a certain cell given two conditions.

How do I return the value in column Sold for the following conditions:
Product = Sink
Location = New York

A vLookup wouldn't work because of multiple conditions. There should only
be one entry with those matching criterias.

Named Ranges are the columns based on their headers.

Product Location Total Sold InStock Percentage
Toaster New York 4 2 2 50%
Oven Philly 17 0 17 100%
Toaster New York 10 2 8 80%
Micro Pittsburgh 10 5 5 50%
Micro Toledo 5 0 5 100%
Sink New York B
Toaster Philly 4 1 3 75%
Toaster Philly 87 15 72 83%
Oven Pittsburgh 9 8 1 11%
Sink Toledo 10 5 5 50%
Toaster Philly A
Toaster Toledo A

BTW This newsgroup is awesome - it's helped me so much in the past few
weeks! (And the boss thinks it's all me!!!)

 

[N Harkawat]

=index(c1:c1000,match(true,((a:a1000="Sink")*(b1:b1000="new york")),0))
array entered (ctrl+shift+enter)

where column C =Total Sold, Col B = Location and Column A = Product

 

[Duke Carey]

Try:

=SUMPRODUCT(--(PRODUCT="Sink"),--(Location="New York"),Sold)

 

[Andy]

Duke,
I'm keeping you busy this morning. This formula works well for summing, but
I don't want to sum, just return the value in that cell. So the value I'm
looking for in this case is B. The value of the cell in the Total range.
Not the sum, but the actual value in the matching cell.

Sorry if the explanation is confusing.
Andy

 

[Duke Carey]

Yes, it sums, Andy. However, since you said there is only one entry that
matches your two criteria, the formula will sum a single cell. Hence, you
should get nothing more nor nothing less than the value you need.

If you aren't correct in your assertion that there will always be only one
matching row, then...that's another story.

 

[Bob Phillips]

If they combination is unique as you say, it will only sum the single value.
QED.

 

[Andy]

The combinations should be unique.

However, the values are not numbers. They can be text at times. Actually,
more often than not, they are text. It's more like a vlookup than a
conditional sum. Any ideas?

Thanks,
Andrea

 

[Duke Carey]

If that's the case, then insert a new column to the left of your table and,
in that column, concatenate the two Product & Location data, with something
like

=A2&B2

Then, the lookup will use the entire range, including the new column, and
you'd use

=VLOOKUP("SinkNewYork",table_range,col#,false)