Lookup a value that fits two (2) criterion

[Carlos R]

I need to find a value that is identified by 2 criterion. For example, I need
to identify the profit of brand y in store 91 (7.0). A vlookup only considers
one criterion, so I need to crossreference with another.

Store Brand Sales Profit Index
91 x 12.6 6.5 0.9
91 y 11.3 7.0 0.7
91 z 19.4 8.3 1
33 x 11.3 7.1 0.6
33 y 10.0 7.0 0.9
33 z 14.0 7.3 0.8

Thank you for your help

 

[Lars-Åke Aspelin]

On Sun, 7 Feb 2010 14:01:01 -0800, Carlos R <Carlos

I need to find a value that is identified by 2 criterion. For example, I need
to identify the profit of brand y in store 91 (7.0). A vlookup only considers
one criterion, so I need to crossreference with another.

Store Brand Sales Profit Index
91 x 12.6 6.5 0.9
91 y 11.3 7.0 0.7
91 z 19.4 8.3 1
33 x 11.3 7.1 0.6
33 y 10.0 7.0 0.9
33 z 14.0 7.3 0.8

Thank you for your help

Try this formula:

=SUMPRODUCT(--(A2:A7=91),--(B2:B7="y"),D27)

Hope this helps / Lars-Åke

 

[Mike H]

Hi,

Use this ARRAY formula.
=INDEX(D27,MATCH(1,(A2:A7=91)*(B2:B7="y"),0))

In Practice you would be better putting the lookup values in a cell
=INDEX(D27,MATCH(1,(A2:A7=G1)*(B2:B7=G2),0))


This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Andri]

Dear Mike,

how if we would like to know....

the first occurance of Brand X, profit = 6.5
then we need the second occurance of Brand X, profit = 7.1

As if we use Vlookup, only get the first profit = 6.5.
On the other hand, we need the second occurance also..

Please guide further.

Respectfully,
andri

 

[Lars-Åke Aspelin]

Try this formula:

=INDEX(D17,LARGE((B1:B7="x")*ROW(B1:B7),COUNTIF(B1:B7,"x")+1-N))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Replace N with 1 for the first, 2 for the second, etc occurance of "x"
in column B. The formula will return the corresponding value from the
D column.

Hope this helps / Lars-Åke