Lookup a value that fits two (2) criterion

  • Thread starter Thread starter Carlos R
  • Start date Start date
C

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
 
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
Click to expand...


Try this formula:

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

Hope this helps / Lars-Åke
 
Hi,

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

In Practice you would be better putting the lookup values in a cell
=INDEX(D2:D7,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.
 
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
 
Try this formula:

=INDEX(D1:D7,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
 
Back
Top