LOOKUP Help!!!

[Guest]

I am trying to return numbers (which will total together) for specific text.
I want the formula to look for specific text (in one column) and return the
number that correspondes with that text. For Example:

=LOOKUP("CHURCHILL",$A17:$A50,C$17:C$65)+LOOKUP("JAGO",$A17:$A50,C$17:C$65)

the problem I'm running into is that if there is no match for the text, the
formula automatically returns the previous number. I just want it to return
a zero if there is no match. Please help if you can

 

[Tom Ogilvy]

=If(iserror(VLOOKUP("CHURCHILL",$A17:$C50,3,False)),0,VLOOKUP("CHURCHILL",$A
17:$C50,3,False))+If(iserror(VLOOKUP("JAGO",$A17:$C50,3,False)),0,VLOOKUP("J
AGO",$A17:$C50,3,False))


Your two ranges should be the same length in cells. Maybe that was a typo.

 

[Kremti]

I am trying to return numbers (which will total together) for specific text.
I want the formula to look for specific text (in one column) and return the
number that correspondes with that text. For Example:

=LOOKUP("CHURCHILL",$A17:$A50,C$17:C$65)+LOOKUP("JAGO",$A17:$A50,C$17:C$65)

the problem I'm running into is that if there is no match for the text, the
formula automatically returns the previous number. I just want it to return
a zero if there is no match. Please help if you can

You might want to use the VLOOKUP function to force the exact match,
and IF and ISERROR to return a zero. The first half of your formula
should look like

=IF(ISERROR(VLOOKUP("CHURCHILL",$A$17:$C$65,3,FALSE),0,VLOOKUP("CHURCHILL",$A$17:$C$65,3,FALSE)))
+ ...

VLOOKUP will search the string "CHURCHILL" in the left most column of
the array going from A17 to C65 (A column from 17 to 65), and returns
the matching value from the 3rd column (C), and FALSE bit there forces
the exact match. If the exact match fails, VLOOKUP will return error,
so IF it is error, IF returns 0, else it returns the VLOOKUP value.

Now, I'm not sure why you have the size of two vectors in your original
LOOKUP function different (i.e., going from A17 to A50 for looking up,
but C17 to C65 for return value)...So you might have to figure out
something with the array you have there.

You can also do something like it with INDEX and MATCH functions as
well, I think.

-K

 

[Guest]

Tom - Thank you very much, it worked, except it finally came back and said
formula is too long (there's over 50 names). Is there any way around this?

 

[Tom Ogilvy]

=SUMPRODUCT(SUMIF($A$17:$A$50,Sheet1!A1:A50,$A$17:$A$50))

add another sheet (sheet1), then enter your 50 names there in A1:A50.

Locations are examples (it doesn't have to be on another sheet). Alter to
suit you situation.