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

A

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

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
 
A

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
 
D

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.
 
B

Bob Phillips

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

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
 
D

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top