Lookup function for a value that has multiple instances

[Guest]

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 Petus]

=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

 

[Guest]

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

 

[Ardus Petus]

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

 

[wiredwrx]

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

--
AP

"Rafat" <[email protected]> a écrit dans le message d
(e-mail address removed)...

Ardus, is there a way to use this similar to a VLOOKUP function.
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 matc
simialr to dragging the VLOOPUP function.

Can this be done.

Thanks in advance for the help,
Michae