Lookup function for a value that has multiple instances

I am working on a case that is of the following similar nature:

Col A Col B Col C
Row 1 Apple 1 ABC
Row 2 Bananna 2 DEF
Row 3 Cherry 1 GHI
Row 4 Cherry 4 JKL
Row 5 Cherry 6 MNO
Row 6 Strawberry 3 PQR

I want to lookup the value in Col C that has "Cherry" in Col A and "6" in
'Col C'. Ofcourse the answer is "MNO", but I am unable to accomplish this
with VLOOKUP. Is there any alternative solution to this.

Thanks,
Rafat Inayat Elahi

=INDEX($C$1:$C$6,MATCH(1,(A1:A6="Cherry")*(B1:B6=6),0))
This is an array formula, to be validated with Ctrl+Shift+Enter, not just
Enter.

HTH
--
AP

"Rafat" <Rafat@discussions.microsoft.com> a écrit dans le message de news:
0CB10A4F-0A4D-456D-975B-FDCC3C6A0AEE@microsoft.com...
>I am working on a case that is of the following similar nature:
>
> Col A Col B Col C
> Row 1 Apple 1 ABC
> Row 2 Bananna 2 DEF
> Row 3 Cherry 1 GHI
> Row 4 Cherry 4 JKL
> Row 5 Cherry 6 MNO
> Row 6 Strawberry 3 PQR
>
> I want to lookup the value in Col C that has "Cherry" in Col A and "6" in
> 'Col C'. Ofcourse the answer is "MNO", but I am unable to accomplish this
> with VLOOKUP. Is there any alternative solution to this.
>
> Thanks,
> Rafat Inayat Elahi

Isnt it possible to have the results in one cell rather than displaying a
full column of a single result?

"Ardus Petus" wrote:

> =INDEX($C$1:$C$6,MATCH(1,(A1:A6="Cherry")*(B1:B6=6),0))
> This is an array formula, to be validated with Ctrl+Shift+Enter, not just
> Enter.
>
> HTH
> --
> AP
>
> "Rafat" <Rafat@discussions.microsoft.com> a écrit dans le message de news:
> 0CB10A4F-0A4D-456D-975B-FDCC3C6A0AEE@microsoft.com...
> >I am working on a case that is of the following similar nature:
> >
> > Col A Col B Col C
> > Row 1 Apple 1 ABC
> > Row 2 Bananna 2 DEF
> > Row 3 Cherry 1 GHI
> > Row 4 Cherry 4 JKL
> > Row 5 Cherry 6 MNO
> > Row 6 Strawberry 3 PQR
> >
> > I want to lookup the value in Col C that has "Cherry" in Col A and "6" in
> > 'Col C'. Ofcourse the answer is "MNO", but I am unable to accomplish this
> > with VLOOKUP. Is there any alternative solution to this.
> >
> > Thanks,
> > Rafat Inayat Elahi
>
>
>

The formula I gave you should be entered in ONE cell
(and validated with Ctrl+Shift+Enter)

--
AP

"Rafat" <Rafat@discussions.microsoft.com> a écrit dans le message de news:
3B71E3D4-854D-49E0-8270-3A04DB8FC55B@microsoft.com...
> Isnt it possible to have the results in one cell rather than displaying a
> full column of a single result?
>
> "Ardus Petus" wrote:
>
>> =INDEX($C$1:$C$6,MATCH(1,(A1:A6="Cherry")*(B1:B6=6),0))
>> This is an array formula, to be validated with Ctrl+Shift+Enter, not just
>> Enter.
>>
>> HTH
>> --
>> AP
>>
>> "Rafat" <Rafat@discussions.microsoft.com> a écrit dans le message de
>> news:
>> 0CB10A4F-0A4D-456D-975B-FDCC3C6A0AEE@microsoft.com...
>> >I am working on a case that is of the following similar nature:
>> >
>> > Col A Col B Col C
>> > Row 1 Apple 1 ABC
>> > Row 2 Bananna 2 DEF
>> > Row 3 Cherry 1 GHI
>> > Row 4 Cherry 4 JKL
>> > Row 5 Cherry 6 MNO
>> > Row 6 Strawberry 3 PQR
>> >
>> > I want to lookup the value in Col C that has "Cherry" in Col A and "6"
>> > in
>> > 'Col C'. Ofcourse the answer is "MNO", but I am unable to accomplish
>> > this
>> > with VLOOKUP. Is there any alternative solution to this.
>> >
>> > Thanks,
>> > Rafat Inayat Elahi
>>
>>
>>

Originally Posted by Ardus Petus

The formula I gave you should be entered in ONE cell
(and validated with Ctrl+Shift+Enter)

--
AP

"Rafat" <Rafat@discussions.microsoft.com> a écrit dans le message de news:
3B71E3D4-854D-49E0-8270-3A04DB8FC55B@microsoft.com...
> Isnt it possible to have the results in one cell rather than displaying a
> full column of a single result?
>
> "Ardus Petus" wrote:
>
>> =INDEX($C$1:$C$6,MATCH(1,(A1:A6="Cherry")*(B1:B6=6),0))
>> This is an array formula, to be validated with Ctrl+Shift+Enter, not just
>> Enter.
>>
>> HTH
>> --
>> AP
>>
>> "Rafat" <Rafat@discussions.microsoft.com> a écrit dans le message de
>> news:
>> 0CB10A4F-0A4D-456D-975B-FDCC3C6A0AEE@microsoft.com...
>> >I am working on a case that is of the following similar nature:
>> >
>> > Col A Col B Col C
>> > Row 1 Apple 1 ABC
>> > Row 2 Bananna 2 DEF
>> > Row 3 Cherry 1 GHI
>> > Row 4 Cherry 4 JKL
>> > Row 5 Cherry 6 MNO
>> > Row 6 Strawberry 3 PQR
>> >
>> > I want to lookup the value in Col C that has "Cherry" in Col A and "6"
>> > in
>> > 'Col C'. Ofcourse the answer is "MNO", but I am unable to accomplish
>> > this
>> > with VLOOKUP. Is there any alternative solution to this.
>> >
>> > Thanks,
>> > Rafat Inayat Elahi
>>
>>
>>

Ardus, is there a way to use this similar to a VLOOKUP function. I want to search for matches of 2 cells in a row in a large spreadsheet, but I want to automatically get the contents of the cells to match simialr to dragging the VLOOPUP function.

Can this be done.

Thanks in advance for the help,
Michael