Double column lookup formula Help
 

Is there a way to have the lookup function look at multiple (2 columns) and
return a
value?

For example:
Column A - I have different names appearing multiple times, for example,
Trade. In Column B, there are other references that appear multiple times
that I need to associate with Trade in a Look up, for example California or
Alaska. In other words there are several subcategories of trade in Column B
and I want to be able to specify a double column lookup so that Excel doesn't
take the first occurrence as is the VLookup limitation.
Let's say in Column A, we have Trade (twice) but in Column B we would have
5 subcategories of Trade such as Alaska and California etc etc. I want to be
able to look up Trade as one part of the formula and then specify the other
variable (such as California) from the next Column. So I want Trade and
California to be one lookup. Another time, I might want Trade and Alaska.
See example below where I want to have my formula in another sheet that will
pull from the reference Trade from Column A and the reference California
from Column B:

Column A Column B
Trade Alaska
DTY Some
DTD Some
Trade California

Thanks!

Double column lookup formula Help
 

Use VLOOKUP
best wishes

--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"JE" wrote in message
...
Is there a way to have the lookup function look at multiple (2 columns)
and
return a
value?

For example:
Column A - I have different names appearing multiple times, for example,
Trade. In Column B, there are other references that appear multiple times
that I need to associate with Trade in a Look up, for example California
or
Alaska. In other words there are several subcategories of trade in Column
B
and I want to be able to specify a double column lookup so that Excel
doesn't
take the first occurrence as is the VLookup limitation.
Let's say in Column A, we have Trade (twice) but in Column B we would
have
5 subcategories of Trade such as Alaska and California etc etc. I want to
be
able to look up Trade as one part of the formula and then specify the
other
variable (such as California) from the next Column. So I want Trade and
California to be one lookup. Another time, I might want Trade and
Alaska.
See example below where I want to have my formula in another sheet that
will
pull from the reference Trade from Column A and the reference California
from Column B:

Column A Column B
Trade Alaska
DTY Some
DTD Some
Trade California

Thanks!

Double column lookup formula Help
 

You don't actually say where the data to be returned is located so I'll it's
column C.

E2 = Trade
F2 = California

Array entered** :

=INDEX(C2:C5,MATCH(1,(A2:A5=E2)*(B2:B5=F2),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the combinations are unique...

Trade and California appear only once *and* the data to be returned is
numeric:

=SUMPRODUCT(--(A2:A5=E2),--(B2:B5=F2),C2:C5)


--
Biff
Microsoft Excel MVP


"JE" wrote in message
...
Is there a way to have the lookup function look at multiple (2 columns)
and
return a
value?

For example:
Column A - I have different names appearing multiple times, for example,
Trade. In Column B, there are other references that appear multiple times
that I need to associate with Trade in a Look up, for example California
or
Alaska. In other words there are several subcategories of trade in Column
B
and I want to be able to specify a double column lookup so that Excel
doesn't
take the first occurrence as is the VLookup limitation.
Let's say in Column A, we have Trade (twice) but in Column B we would
have
5 subcategories of Trade such as Alaska and California etc etc. I want to
be
able to look up Trade as one part of the formula and then specify the
other
variable (such as California) from the next Column. So I want Trade and
California to be one lookup. Another time, I might want Trade and
Alaska.
See example below where I want to have my formula in another sheet that
will
pull from the reference Trade from Column A and the reference California
from Column B:

Column A Column B
Trade Alaska
DTY Some
DTD Some
Trade California

Thanks!