Lookup Problem Matching on Two Criteria

I have a lookup/match problem that I cannot seem to get a solution to.
I am assuming I can use a combination IF statement with a nested
VLOOKUP but it's not coming to me.

I have a table with three columns. Column A has values S01 to S08.
Column B has unique values but are associated with the values in
column A. Column C has the target values I am trying to populate in a
cell.

So the problem is that I need to match a Column A value with a column
B value to find the answer in column C. Please note that the values in
Column A are duplicated multiple times depending on their relationship
to the values in column B.

Any suggestions would be appreciated. Thanks.

=INDEX(C1:C100,MATCH(1,(A1:A100="S01")*(B1:B100="xxx"),0))

it is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mike" <NbrCruncher@gmail.com> wrote in message
news:1175011014.064417.134390@y80g2000hsf.googlegroups.com...
>I have a lookup/match problem that I cannot seem to get a solution to.
> I am assuming I can use a combination IF statement with a nested
> VLOOKUP but it's not coming to me.
>
> I have a table with three columns. Column A has values S01 to S08.
> Column B has unique values but are associated with the values in
> column A. Column C has the target values I am trying to populate in a
> cell.
>
> So the problem is that I need to match a Column A value with a column
> B value to find the answer in column C. Please note that the values in
> Column A are duplicated multiple times depending on their relationship
> to the values in column B.
>
> Any suggestions would be appreciated. Thanks.
>

Mike,

If the values in column C are numeric:

=SUMPRODUCT((A1:A100="S01")*(B1:B100="Whatever")*C1:C100)

If they are strings:

=INDEX(C1:C100,SUMPRODUCT((A1:A100="S01")*(B1:B100="Whatever")*ROW(C1:C100)))

HTH,
Bernie
MS Excel MVP


"Mike" <NbrCruncher@gmail.com> wrote in message
news:1175011014.064417.134390@y80g2000hsf.googlegroups.com...
>I have a lookup/match problem that I cannot seem to get a solution to.
> I am assuming I can use a combination IF statement with a nested
> VLOOKUP but it's not coming to me.
>
> I have a table with three columns. Column A has values S01 to S08.
> Column B has unique values but are associated with the values in
> column A. Column C has the target values I am trying to populate in a
> cell.
>
> So the problem is that I need to match a Column A value with a column
> B value to find the answer in column C. Please note that the values in
> Column A are duplicated multiple times depending on their relationship
> to the values in column B.
>
> Any suggestions would be appreciated. Thanks.
>

On Mar 27, 12:16 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:
> Mike,
>
> If the values in column C are numeric:
>
> =SUMPRODUCT((A1:A100="S01")*(B1:B100="Whatever")*C1:C100)
>
> If they are strings:
>
> =INDEX(C1:C100,SUMPRODUCT((A1:A100="S01")*(B1:B100="Whatever")*ROW(C1:C100)*))
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Mike" <NbrCrunc...@gmail.com> wrote in message
>
> news:1175011014.064417.134390@y80g2000hsf.googlegroups.com...
>
>
>
> >I have a lookup/match problem that I cannot seem to get a solution to.
> > I am assuming I can use a combination IF statement with a nested
> > VLOOKUP but it's not coming to me.
>
> > I have a table with three columns. Column A has values S01 to S08.
> > Column B has unique values but are associated with the values in
> > column A. Column C has the target values I am trying to populate in a
> > cell.
>
> > So the problem is that I need to match a Column A value with a column
> > B value to find the answer in column C. Please note that the values in
> > Column A are duplicated multiple times depending on their relationship
> > to the values in column B.
>
> > Any suggestions would be appreciated. Thanks.- Hide quoted text -
>
> - Show quoted text -

Thank you for your answers, however I get errors in both cases which
may have to do with me not making the problem clear enough.

I have a column in a report which I want to populate with a numeric
value contained in a different worksheet(tab). The source worksheet
has three columns (A,B,C). Column A is alpha-numeric with duplicative
values. Column's B & C are numeric. Column B has unique values but
they have to correspond to a value in column A in order to get the
right answer in column C.

i.e.
Record1 - ColumnA(S01) ColumnB(29) ColumnC(125)
Record2 - ColumnA(S01) ColumnB(14) ColumnC(535)
Record3 - ColumnA(S02) CoulmnB(71) ColumnC(55)

My formula should determine that record 2 has the right combination
(S01 + 14) in order to arrive at the answer (535). Does this make more
sense?

In my report, the formula would have to evaluate the contents of a
column with data in ColumnA and match that to the contents of a column
containing values in columnB in order to get the answer for that
unique combination found in column C.

This should work - no quotes needed since column B in numeric:

=SUMPRODUCT((A1:A100="S01")*(B1:B100=14)*C1:C100)


HTH,
Bernie
MS Excel MVP

Thank you for your answers, however I get errors in both cases which
may have to do with me not making the problem clear enough.

I have a column in a report which I want to populate with a numeric
value contained in a different worksheet(tab). The source worksheet
has three columns (A,B,C). Column A is alpha-numeric with duplicative
values. Column's B & C are numeric. Column B has unique values but
they have to correspond to a value in column A in order to get the
right answer in column C.

i.e.
Record1 - ColumnA(S01) ColumnB(29) ColumnC(125)
Record2 - ColumnA(S01) ColumnB(14) ColumnC(535)
Record3 - ColumnA(S02) CoulmnB(71) ColumnC(55)

My formula should determine that record 2 has the right combination
(S01 + 14) in order to arrive at the answer (535). Does this make more
sense?

In my report, the formula would have to evaluate the contents of a
column with data in ColumnA and match that to the contents of a column
containing values in columnB in order to get the answer for that
unique combination found in column C.

On Mar 27, 12:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:
> This should work - no quotes needed since column B in numeric:
>
> =SUMPRODUCT((A1:A100="S01")*(B1:B100=14)*C1:C100)
>
> HTH,
> Bernie
> MS Excel MVP
>
> Thank you for your answers, however I get errors in both cases which
> may have to do with me not making the problem clear enough.
>
> I have a column in a report which I want to populate with a numeric
> value contained in a different worksheet(tab). The source worksheet
> has three columns (A,B,C). Column A is alpha-numeric with duplicative
> values. Column's B & C are numeric. Column B has unique values but
> they have to correspond to a value in column A in order to get the
> right answer in column C.
>
> i.e.
> Record1 - ColumnA(S01) ColumnB(29) ColumnC(125)
> Record2 - ColumnA(S01) ColumnB(14) ColumnC(535)
> Record3 - ColumnA(S02) CoulmnB(71) ColumnC(55)
>
> My formula should determine that record 2 has the right combination
> (S01 + 14) in order to arrive at the answer (535). Does this make more
> sense?
>
> In my report, the formula would have to evaluate the contents of a
> column with data in ColumnA and match that to the contents of a column
> containing values in columnB in order to get the answer for that
> unique combination found in column C.

Thanks Bernie. That worked. I now have learned a new way to use
SumProduct.