avoid nested IFs with another function

[pda]

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.

 

[Ron Rosenfeld]

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

 

[Peo Sjoblom]

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

 

[Sheeloo]

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]

This worked perfectly! Amazing! Thanks!

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