| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
avoid nested IFs with another function
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
avoid nested IFs with another function
On Sat, 23 Aug 2008 14:20:01 -0700, 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. Check out the VLOOKUP worksheet function. --ron |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
avoid nested IFs with another function
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" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
avoid nested IFs with another function
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
avoid nested IFs with another function
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" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using sum function,can I avoid calculating hidden rows | Excel Worksheet Functions | |||
| ? avoid changing sum function as rows added? | New Users to Excel | |||
| how can avoid considering of blank cells in IF function | Excel Worksheet Functions | |||
| How do you avoid duplicates when using the randbetween function? | Excel Worksheet Functions | |||
| Avoid geeting function GETPIVOTDATA | Excel Discussion (Misc queries) | |||
Linear Mode
