Match / Index off two "keys"

[carlyman]

I need a way to find a value (could be text) in column C based on the values in column A and column B. Entries in A and entires in B are not unique by themselves; however, by "keying" off columns A and B together, only one entry in column C results.

Example:
A-----B-----C
D-----X-----Test1
D-----Y-----Test2
F-----Y-----Test3

I've tried various index and match combinations, but haven't figured it out yet. Any ideas?

Thanks,
JC

[Gary''s Student]

The easiest way is to create a "helper" column concatinating columns A and B.
The items in the new column would look like:

DX
DY
FY

This way you can use a simple MATCH and INDEX or VLOOKUP as you please.

This technique is not limited to two columns by the way.

Good Luck
--
Gary''s Student


"carlyman" wrote:

>
> I need a way to find a value (could be text) in column C based on the
> values in column A and column B. Entries in A and entires in B are not
> unique by themselves; however, by "keying" off columns A and B together,
> only one entry in column C results.
>
> Example:
> A-----B-----C
> D-----X-----Test1
> D-----Y-----Test2
> F-----Y-----Test3
>
> I've tried various index and match combinations, but haven't figured it
> out yet. Any ideas?
>
> Thanks,
> JC
>
>
> --
> carlyman
> ------------------------------------------------------------------------
> carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
> View this thread: http://www.excelforum.com/showthread...hreadid=466313
>
>

[bj]

try sumproduct()
=sumproduct(--(rangeA=criteria 1),--(rangeB=criteria 2),RangeC)
the --( changes the logic true false to a 1 0 numeric
the arrays in each section need to be the same size but the shorthand for a
full column or row will not work (A:A won't work)

"carlyman" wrote:

>
> I need a way to find a value (could be text) in column C based on the
> values in column A and column B. Entries in A and entires in B are not
> unique by themselves; however, by "keying" off columns A and B together,
> only one entry in column C results.
>
> Example:
> A-----B-----C
> D-----X-----Test1
> D-----Y-----Test2
> F-----Y-----Test3
>
> I've tried various index and match combinations, but haven't figured it
> out yet. Any ideas?
>
> Thanks,
> JC
>
>
> --
> carlyman
> ------------------------------------------------------------------------
> carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
> View this thread: http://www.excelforum.com/showthread...hreadid=466313
>
>

[B. R.Ramachandran]

Hi,
Try the following formula in say F2; the assumes that the data are in rows 2
thru 1000, and, D2 and E2 are the search criteria.

=OFFSET(C1,SUMPRODUCT(--(A2:A1000=D2)*--(B2:B1000=E2)*ROW(C2:C1000))-1,0)

Regards,
B. R. Ramachandran


"carlyman" wrote:

>
> I need a way to find a value (could be text) in column C based on the
> values in column A and column B. Entries in A and entires in B are not
> unique by themselves; however, by "keying" off columns A and B together,
> only one entry in column C results.
>
> Example:
> A-----B-----C
> D-----X-----Test1
> D-----Y-----Test2
> F-----Y-----Test3
>
> I've tried various index and match combinations, but haven't figured it
> out yet. Any ideas?
>
> Thanks,
> JC
>
>
> --
> carlyman
> ------------------------------------------------------------------------
> carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
> View this thread: http://www.excelforum.com/showthread...hreadid=466313
>
>

[Domenic]

Try...

=INDEX(C1:C3,MATCH(1,(A1:A3="D")*(B1:B3="Y"),0))

or

=INDEX(C1:C3,MATCH(1,(A1:A3=E1)*(B1:B3=F1),0))

....where E1 contains your first criterion, such as 'D', and F1 contains
your second criterion, such as 'Y'.

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!

In article <[email protected]>,
carlyman <[email protected]>
wrote:

> I need a way to find a value (could be text) in column C based on the
> values in column A and column B. Entries in A and entires in B are not
> unique by themselves; however, by "keying" off columns A and B together,
> only one entry in column C results.
>
> Example:
> A-----B-----C
> D-----X-----Test1
> D-----Y-----Test2
> F-----Y-----Test3
>
> I've tried various index and match combinations, but haven't figured it
> out yet. Any ideas?
>
> Thanks,
> JC

[B. R.Ramachandran]

Hi,

It is a clever idea to concatenate two (or more) columns before using
VLOOKUP etc. However, one has to be careful: eg, "donald" & "rich" and "don"
& "aldrich" concatenate identically (unless you include a differentiating
feature, e.g., a space between the two substrings, while concatenating).

Regards,
B. R. Ramachandran

"Gary''s Student" wrote:

> The easiest way is to create a "helper" column concatinating columns A and B.
> The items in the new column would look like:
>
> DX
> DY
> FY
>
> This way you can use a simple MATCH and INDEX or VLOOKUP as you please.
>
> This technique is not limited to two columns by the way.
>
> Good Luck
> --
> Gary''s Student
>
>
> "carlyman" wrote:
>
> >
> > I need a way to find a value (could be text) in column C based on the
> > values in column A and column B. Entries in A and entires in B are not
> > unique by themselves; however, by "keying" off columns A and B together,
> > only one entry in column C results.
> >
> > Example:
> > A-----B-----C
> > D-----X-----Test1
> > D-----Y-----Test2
> > F-----Y-----Test3
> >
> > I've tried various index and match combinations, but haven't figured it
> > out yet. Any ideas?
> >
> > Thanks,
> > JC
> >
> >
> > --
> > carlyman
> > ------------------------------------------------------------------------
> > carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
> > View this thread: http://www.excelforum.com/showthread...hreadid=466313
> >
> >