Lookup function for a value that has multiple instances

R

Rafat

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
 
A

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
 
R

Rafat

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

Ardus Petus

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

wiredwrx

Ardus said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top