Lookup function for a value that has multiple instances
 

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

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

Lookup function for a value that has multiple instances
 

=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" a écrit dans le message de news:
...
I am working on a case that is of the following similar natu

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

Lookup function for a value that has multiple instances
 

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" a écrit dans le message de news:
...
I am working on a case that is of the following similar natu

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

Lookup function for a value that has multiple instances
 

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

--
AP

"Rafat" a écrit dans le message de news:
...
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" a écrit dans le message de
news:
...
I am working on a case that is of the following similar natu

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

Lookup function for a value that has multiple instances
 

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

--
AP

"Rafat" a écrit dans le message de
news:
...
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" a écrit dans le message
de
news:
...
I am working on a case that is of the following similar natu

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


--
wiredwrx
------------------------------------------------------------------------
wiredwrx's Profile: http://www.excelforum.com/member.php...o&userid=21145
View this thread: http://www.excelforum.com/showthread...hreadid=549958