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!

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!

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!