Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or IF Function?
Is it possible to use the VLOOKUP or IF function to firstly, search a column
for a particular word, then return the corresponding values from another column and calculate multiple values if more than one word hit is found? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or IF Function?
Carol,
A bit short on info but this looks in column A for the valkue in C1 and then sums any corresponding values in column B. =SUMIF(A:A,C1,B:B) Mike "carol" wrote: Is it possible to use the VLOOKUP or IF function to firstly, search a column for a particular word, then return the corresponding values from another column and calculate multiple values if more than one word hit is found? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or IF Function?
Hi Carol,
Not sure how is your data looking like, but you can use combination of IF, VLOOKUP, and COUNT HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "carol" wrote: Is it possible to use the VLOOKUP or IF function to firstly, search a column for a particular word, then return the corresponding values from another column and calculate multiple values if more than one word hit is found? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or IF Function?
Try sumproduct ..
Eg: =SUMPRODUCT((A2:A100="hit")*B2:B100) will return the sum of numbers in col B where col A contain the key text: hit And if the key text: hit could be part of a text string, eg: hit man, double hit, etc you could use instead: =SUMPRODUCT(ISNUMBER(SEARCH("hit",A2:A100))*B2:B10 0) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "carol" wrote: Is it possible to use the VLOOKUP or IF function to firstly, search a column for a particular word, then return the corresponding values from another column and calculate multiple values if more than one word hit is found? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or IF Function?
VLOOKUP is excellent for retrieving a single value, if one occurrence
of lookup value is expected. If you expect several occurrences and want a sum of the results you will probably need SUMIF: =SUMIF(B2:B100,"AJ-123",D2:D100) This formula will search column B:B for occurrences of "AJ-123" and sum corresponding entries in D:D. HTH Kostis Vezerides On Dec 10, 5:03 pm, carol wrote: Is it possible to use the VLOOKUP or IF function to firstly, search a column for a particular word, then return the corresponding values from another column and calculate multiple values if more than one word hit is found? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or IF Function?
Thanks max, that did it.
Sorry to everyone else for my info being vague! "Max" wrote: Try sumproduct .. Eg: =SUMPRODUCT((A2:A100="hit")*B2:B100) will return the sum of numbers in col B where col A contain the key text: hit And if the key text: hit could be part of a text string, eg: hit man, double hit, etc you could use instead: =SUMPRODUCT(ISNUMBER(SEARCH("hit",A2:A100))*B2:B10 0) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "carol" wrote: Is it possible to use the VLOOKUP or IF function to firstly, search a column for a particular word, then return the corresponding values from another column and calculate multiple values if more than one word hit is found? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or IF Function?
welcome, glad you got what you were after.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "carol" wrote in message ... Thanks max, that did it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Embedded VLOOKUP function within IF function | Excel Worksheet Functions | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions |