Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with more than 1 criteria
Dog brown 25
Dog Grey 28 Cat Blue 30 Rabbit Brown 12 Dog grey 18 Can the vlookup function be used to lookup more than one value in a row and return a single corresponding value? In the example below I want to return the value 25. But if I lookup "dog", there's two and if I look up "brown" there's also two so I need to lookup dog and brown together? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with more than 1 criteria
How about:-
=SUMPRODUCT((A1:A5="Dog")*(B1:B5="Brown")*(C1:C5)) You don't say what you want if you searched for Grey dogs, this would return 46 Mike "EoinL" wrote: Dog brown 25 Dog Grey 28 Cat Blue 30 Rabbit Brown 12 Dog grey 18 Can the vlookup function be used to lookup more than one value in a row and return a single corresponding value? In the example below I want to return the value 25. But if I lookup "dog", there's two and if I look up "brown" there's also two so I need to lookup dog and brown together? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with more than 1 criteria
If you want to lookup and return only the 1st match for the dual criteria
Array-entered, press CTRL+SHIFT+ENTER to confirm the formula: =INDEX(C1:C5,MATCH(1,(A1:A5="Dog")*(B1:B5="Grey"), 0)) Based on your sample data, above will return: 28 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "EoinL" wrote: Dog brown 25 Dog Grey 28 Cat Blue 30 Rabbit Brown 12 Dog grey 18 Can the vlookup function be used to lookup more than one value in a row and return a single corresponding value? In the example below I want to return the value 25. But if I lookup "dog", there's two and if I look up "brown" there's also two so I need to lookup dog and brown together? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP or IF with many criteria | Excel Worksheet Functions | |||
vlookup with two criteria | Excel Worksheet Functions | |||
vlookup with two criteria | Excel Worksheet Functions | |||
VLOOKUP with MAX criteria | Excel Discussion (Misc queries) | |||
vlookup with two criteria | Excel Worksheet Functions |