Re: LOOKUP Help!!!

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

kimberly_churchill wrote:
> 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

.

Relevant Pages

  • RE: excel lookup functions
    ... VLOOKUP to work correctly: ... it sounds like you are looking for an EXACT match, ... your VLOOKUP function should look something like this: ...
    (microsoft.public.excel.misc)
  • RE: excel lookup functions
    ... VLOOKUP to work correctly: ... within the same workbook? ... it sounds like you are looking for an EXACT match, ... your VLOOKUP function should look something like this: ...
    (microsoft.public.excel.misc)
  • RE: excel lookup functions
    ... i have columns A through H. I am looking for 5 digit numbers in column A ... i have tried to use the vlookup formula to return the date in column H. ... it sounds like you are looking for an EXACT match, ... your VLOOKUP function should look something like this: ...
    (microsoft.public.excel.misc)
  • Re: Vlookup Using VBA (without using VLOOKUP function)
    ... Daves Match/Index suggestion took 3.5 seconds, my vlookup 2.4 seconds. ... VBA is the slowest except for an exact match ... Set rngFound = Nothing ... lookup, which are faster using a single exact match lookup. ...
    (microsoft.public.excel.programming)
  • RE: excel lookup functions
    ... it sounds like you are looking for an EXACT match, ... your VLOOKUP function should look something like this: ... if the VLOOKUP does not return an EXACT match, ...
    (microsoft.public.excel.misc)