Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup against pivot table with multiple instances | Excel Worksheet Functions | |||
Help with a lookup function that will give me multiple answers | Excel Discussion (Misc queries) | |||
External Links and Multiple Instances | Excel Discussion (Misc queries) | |||
Multiple criteria LOOKUP | Excel Worksheet Functions | |||
lookup function 1 | Excel Worksheet Functions |