I have an array comprised of two columns and and 8 rows. The left column
contains an ascending list of numbers -- 91, 181, 366, etc. The right column
contains a corresponding text entry. I want to compare if a value is less
than the number in the left column of the first row. IF the compared value
matches the criteria, pull the text in right column, but if not, move on to
the next row, perform the comparison again to the number in the second row
and either pull the text in the right column or move on to row three, and so
on.

91 AGING
181 MILD
366 MEDIUM
732 SHARP - 1YR
1098 SHARP - 2YR
1464 SHARP - 3YR
1830 SHARP - 4YR
2195 SHARP - 5YR & MORE

Can I avoid nesting IF functions? I will have other items that will have
more than 8 rows.

I hope this question makes sense.

Thanks for helping.

On Sat, 23 Aug 2008 14:20:01 -0700, pda <(E-Mail Removed)> wrote:

>I have an array comprised of two columns and and 8 rows. The left column
>contains an ascending list of numbers -- 91, 181, 366, etc. The right column
>contains a corresponding text entry. I want to compare if a value is less
>than the number in the left column of the first row. IF the compared value
>matches the criteria, pull the text in right column, but if not, move on to
>the next row, perform the comparison again to the number in the second row
>and either pull the text in the right column or move on to row three, and so
>on.
>
>91 AGING
>181 MILD
>366 MEDIUM
>732 SHARP - 1YR
>1098 SHARP - 2YR
>1464 SHARP - 3YR
>1830 SHARP - 4YR
>2195 SHARP - 5YR & MORE
>
>Can I avoid nesting IF functions? I will have other items that will have
>more than 8 rows.
>
>I hope this question makes sense.
>
>Thanks for helping.

Check out the VLOOKUP worksheet function.
--ron

Are these exact numbers or can there be 101 for instance and you still want
for instance "MILD" returned. If you are looking for an exact match you can
use


=IF(ISNA(VLOOKUP(A1,B1:C20,2,0)),"no match",VLOOKUP(A1,B1:C20,2,0))

if you want that every integer from greater than 91 to 181 will return MILD
than or everything from 0 to 91 will return AGING then you need to change
the table to something like


0 AGING
92 MILD
181 MEDIUM
367 SHARP - 1YR
733 SHARP - 2YR
1099 SHARP - 3YR
1465 SHARP - 4YR
1831 SHARP - 5YR & MORE

and change the formula to

=IF(ISNA(VLOOKUP(A1,B1:C20,2)),"no match",VLOOKUP(A1,B1:C20,2))



--


Regards,


Peo Sjoblom






"pda" <(E-Mail Removed)> wrote in message
news:C0C2C319-35AC-451C-AC59-(E-Mail Removed)...
>I have an array comprised of two columns and and 8 rows. The left column
> contains an ascending list of numbers -- 91, 181, 366, etc. The right
> column
> contains a corresponding text entry. I want to compare if a value is less
> than the number in the left column of the first row. IF the compared
> value
> matches the criteria, pull the text in right column, but if not, move on
> to
> the next row, perform the comparison again to the number in the second row
> and either pull the text in the right column or move on to row three, and
> so
> on.
>
> 91 AGING
> 181 MILD
> 366 MEDIUM
> 732 SHARP - 1YR
> 1098 SHARP - 2YR
> 1464 SHARP - 3YR
> 1830 SHARP - 4YR
> 2195 SHARP - 5YR & MORE
>
> Can I avoid nesting IF functions? I will have other items that will have
> more than 8 rows.
>
> I hope this question makes sense.
>
> Thanks for helping.

Enter the following in D1 assuming that the number you want to lookup is in C1
=VLOOKUP(C1,A:B,2,True)

Numbers in column A have to be in ascending order for this to work.

"pda" wrote:

> I have an array comprised of two columns and and 8 rows. The left column
> contains an ascending list of numbers -- 91, 181, 366, etc. The right column
> contains a corresponding text entry. I want to compare if a value is less
> than the number in the left column of the first row. IF the compared value
> matches the criteria, pull the text in right column, but if not, move on to
> the next row, perform the comparison again to the number in the second row
> and either pull the text in the right column or move on to row three, and so
> on.
>
> 91 AGING
> 181 MILD
> 366 MEDIUM
> 732 SHARP - 1YR
> 1098 SHARP - 2YR
> 1464 SHARP - 3YR
> 1830 SHARP - 4YR
> 2195 SHARP - 5YR & MORE
>
> Can I avoid nesting IF functions? I will have other items that will have
> more than 8 rows.
>
> I hope this question makes sense.
>
> Thanks for helping.

This worked perfectly! Amazing! Thanks!

"Peo Sjoblom" wrote:

> Are these exact numbers or can there be 101 for instance and you still want
> for instance "MILD" returned. If you are looking for an exact match you can
> use
>
>
> =IF(ISNA(VLOOKUP(A1,B1:C20,2,0)),"no match",VLOOKUP(A1,B1:C20,2,0))
>
> if you want that every integer from greater than 91 to 181 will return MILD
> than or everything from 0 to 91 will return AGING then you need to change
> the table to something like
>
>
> 0 AGING
> 92 MILD
> 181 MEDIUM
> 367 SHARP - 1YR
> 733 SHARP - 2YR
> 1099 SHARP - 3YR
> 1465 SHARP - 4YR
> 1831 SHARP - 5YR & MORE
>
> and change the formula to
>
> =IF(ISNA(VLOOKUP(A1,B1:C20,2)),"no match",VLOOKUP(A1,B1:C20,2))
>
>
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
>
>
>
>
> "pda" <(E-Mail Removed)> wrote in message
> news:C0C2C319-35AC-451C-AC59-(E-Mail Removed)...
> >I have an array comprised of two columns and and 8 rows. The left column
> > contains an ascending list of numbers -- 91, 181, 366, etc. The right
> > column
> > contains a corresponding text entry. I want to compare if a value is less
> > than the number in the left column of the first row. IF the compared
> > value
> > matches the criteria, pull the text in right column, but if not, move on
> > to
> > the next row, perform the comparison again to the number in the second row
> > and either pull the text in the right column or move on to row three, and
> > so
> > on.
> >
> > 91 AGING
> > 181 MILD
> > 366 MEDIUM
> > 732 SHARP - 1YR
> > 1098 SHARP - 2YR
> > 1464 SHARP - 3YR
> > 1830 SHARP - 4YR
> > 2195 SHARP - 5YR & MORE
> >
> > Can I avoid nesting IF functions? I will have other items that will have
> > more than 8 rows.
> >
> > I hope this question makes sense.
> >
> > Thanks for helping.
>
>
>