Lookup Problem Matching on Two Criteria

[Mike]

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.

 

[Bob Phillips]

=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)

 

[Bernie Deitrick]

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]

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









- 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.

 

[Bernie Deitrick]

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.

 

[Mike]

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.