Example:

C1 C2 C3
R1 Apple Fuji 10

I want to create a lookup that will return the value 10 if C1=Apple AND
C2=Fuji.
Can someone help? Thank you.

Create a helper column with Apple and fuji concatenated together. Then do
the lookup (or index) based upon that.

"Sweepea" wrote:

Example:

C1 C2 C3
R1 Apple Fuji 10

I want to create a lookup that will return the value 10 if C1=Apple AND
C2=Fuji.
Can someone help? Thank you.

Is there other options without going the concatenate route? The problem is I
can't really
change the data, can only do a lookup to read the data.

Please advise.


"Barb Reinhardt" wrote:

Create a helper column with Apple and fuji concatenated together. Then do
the lookup (or index) based upon that.

"Sweepea" wrote:

Example:

C1 C2 C3
R1 Apple Fuji 10

I want to create a lookup that will return the value 10 if C1=Apple AND
C2=Fuji.
Can someone help? Thank you.

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

Sweepea wrote:

Example:

C1 C2 C3
R1 Apple Fuji 10

I want to create a lookup that will return the value 10 if C1=Apple AND
C2=Fuji.
Can someone help? Thank you.

--

Dave Peterson

Hi,

A possibility using sumproduct:

=SUMPRODUCT(--(A1:A1="Apple"),--(B1:B1="Fuji"),C1:C1)

The range can be extended e.g. A1:A100, B1:B100, C1:C100 etc.

Hth
Anthony

"Sweepea" wrote:

Example:

C1 C2 C3
R1 Apple Fuji 10

I want to create a lookup that will return the value 10 if C1=Apple AND
C2=Fuji.
Can someone help? Thank you.

Thank you, Anthony. The formula is easy and it really helps.

But I will try Dave's method as well.

Thanks everyone for your help .... most sincerely appreciated.


"Anthony D" wrote:

Hi,

A possibility using sumproduct:

=SUMPRODUCT(--(A1:A1="Apple"),--(B1:B1="Fuji"),C1:C1)

The range can be extended e.g. A1:A100, B1:B100, C1:C100 etc.

Hth
Anthony

"Sweepea" wrote:

Example:

C1 C2 C3
R1 Apple Fuji 10

I want to create a lookup that will return the value 10 if C1=Apple AND
C2=Fuji.
Can someone help? Thank you.

Anthony's formula is a very nice way to sum the values in column C when column A
and column B match the criteria (apple/fuji).

The other formula is more like an =vlookup(). It will return text or number for
the first row that matches the criteria.

Sweepea wrote:

Thank you, Anthony. The formula is easy and it really helps.

But I will try Dave's method as well.

Thanks everyone for your help .... most sincerely appreciated.

"Anthony D" wrote:

Hi,

A possibility using sumproduct:

=SUMPRODUCT(--(A1:A1="Apple"),--(B1:B1="Fuji"),C1:C1)

The range can be extended e.g. A1:A100, B1:B100, C1:C100 etc.

Hth
Anthony

"Sweepea" wrote:

Example:

C1 C2 C3
R1 Apple Fuji 10

I want to create a lookup that will return the value 10 if C1=Apple AND
C2=Fuji.
Can someone help? Thank you.

--

Dave Peterson

You are very welcome, Sweepea, thank you for your feedback.

One additional consideration. As Dave notes, this currently assumes the
criteria are met. If the value in column C for a valid combination like Apple
and Fuji could be 0, there would be no distinction between not finding the
combination (e.g. through inaccurate search critera or data) and finding it,
both giving 0 in that particular case. The expression can be extended to
cover this by verifying the existence of the search criteria in the data.

Anthony

"Sweepea" wrote:

Thank you, Anthony. The formula is easy and it really helps.

But I will try Dave's method as well.

Thanks everyone for your help .... most sincerely appreciated.


"Anthony D" wrote:

Hi,

A possibility using sumproduct:

=SUMPRODUCT(--(A1:A1="Apple"),--(B1:B1="Fuji"),C1:C1)

The range can be extended e.g. A1:A100, B1:B100, C1:C100 etc.

Hth
Anthony

"Sweepea" wrote:

Example:

C1 C2 C3
R1 Apple Fuji 10

I want to create a lookup that will return the value 10 if C1=Apple AND
C2=Fuji.
Can someone help? Thank you.