On Fri, 24 Jun 2005 05:33:04 -0700, "Todd F."
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