Re: Nested IF(AND is not working

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

thanks you much I will study this in next few days and I hope to make use of it

"Ron Rosenfeld" wrote:

> On Fri, 24 Jun 2005 05:33:04 -0700, "Todd F." <ToddF@xxxxxxxxxxxxxxxxxxxxxxxxx>
> wrote:
>
> >this is a very interesting formula , never thought of using vlookup which I
> >am a big fan of . do you feel like explaining this to me or directing me to a
> >place to read
> >about this.
> >
> >thanks for the time
> >
> >
> >"Ron Rosenfeld" wrote:
> >
> >>
> >> Try:
> >>
> >> =VLOOKUP(A2,{0,"<30";31,"31-60";61,"61-90";91,"91+"},2)
> >>
> >>
> >> --ron
> >>
>
> Look at HELP for VLOOKUP.
>
> The part of the formula above that is within the braces is what is called an
> array constant. Commas separate columns and semicolons separate rows.
>
> So A2 is your lookup_value. Your lookup_array could also be a range reference
> looking like:
>
> 0 <30
> 31 31-60
> 61 61-90
> 91 91+
>
> In, let us say, L1:M4.
>
> The "2" at the end of the formula says to find the match in column 2.
>
> So the formula looks for some value (A2) in the leftmost column of the table
> that is either an exact match or, if an exact match is not found, the next
> largest value that is less than lookup_value.
>
> Since, for example, there is no exact match for '15', the largest value in the
> table that is less than 15 is '0'; in column 2 of that row is the "<30" so
> that's what gets returned.
>
> Lookup tables are frequently much more flexible, and easier to modify, than
> complicated IF statements.
>
> If you set up a table as above some place, instead of using the array constant,
> the formula could be rewritten as:
>
> =VLOOKUP(A2,tbl,2)
>
> or
>
> =VLOOKUP(A2,L1:M4,2)
>
>
> --ron
>
.

Relevant Pages

  • Re: Nested IF(AND is not working
    ... Look at HELP for VLOOKUP. ... Commas separate columns and semicolons separate rows. ... that is either an exact match or, if an exact match is not found, the next ... If you set up a table as above some place, instead of using the array constant, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Help with this formula
    ... Really all I am trying to do is to get rid of the #N/A's and copy across ... The table_array is specified by the array constant ... semicolons are row separators. ... exact match of in row 2 column 1 of the table, the b, and returns column ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Help with this formula
    ... The format is: ... The table_array is specified by the array constant ... semicolons are row separators. ... exact match of in row 2 column 1 of the table, the b, and returns column ...
    (microsoft.public.excel.worksheet.functions)
  • 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)