Hi Team,
How to replace nested if formula with vlookup.
Below formula gives me correct result. I want to apply Vlookup how to Create Table for the same.
I have created table for Vlookup purpose but its not giving correct result.
Column B is gives correct Result if tried with IFS, But Column C gives wrong result if tried with Vlookup, Something problem with my Table. plz assist Thanks
Thanks
mg
How to replace nested if formula with vlookup.
Below formula gives me correct result. I want to apply Vlookup how to Create Table for the same.
I have created table for Vlookup purpose but its not giving correct result.
Rich (BB code):
=IF(A3<0,"Not Due",IF(A3<=7,"0-7 Days",IF(A3<=15,"8-15 Days",IF(A3<=30,"16-30 Days",IF(A3<=60,"31-60 Days",IF(A3<=90,"61-90 Days",IF(A3<=120,"91-120 Days",IF(A3<=365,"121-365 Days",">1 Year"))))))))
Column B is gives correct Result if tried with IFS, But Column C gives wrong result if tried with Vlookup, Something problem with my Table. plz assist Thanks
Book2 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Days | Ageing Bucket | Vlookup Result | Table for Vlookup | ||||
2 | -100 | Not Due | #N/A | 0 | Not Due | |||
3 | 366 | >1 Year | 121-365 Days | 7 | 0-7 Days | |||
4 | 8 | 8-15 Days | 0-7 Days | 15 | 8-15 Days | |||
5 | 30 | 16-30 Days | 16-30 Days | 30 | 16-30 Days | |||
6 | 31 | 31-60 Days | 16-30 Days | 60 | 31-60 Days | |||
7 | 90 | 61-90 Days | 61-90 Days | 90 | 61-90 Days | |||
8 | 91 | 91-120 Days | 61-90 Days | 120 | 91-120 Days | |||
9 | 120 | 91-120 Days | 91-120 Days | 365 | 121-365 Days | |||
10 | 121 | 121-365 Days | 91-120 Days | else | >1 Year | |||
11 | 365 | 121-365 Days | 121-365 Days | |||||
12 | 366 | >1 Year | 121-365 Days | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2:B12 | B2 | =IF(A2<0,"Not Due",IF(A2<=7,"0-7 Days",IF(A2<=15,"8-15 Days",IF(A2<=30,"16-30 Days",IF(A2<=60,"31-60 Days",IF(A2<=90,"61-90 Days",IF(A2<=120,"91-120 Days",IF(A2<=365,"121-365 Days",">1 Year")))))))) |
C2:C12 | C2 | =VLOOKUP(A2,$E$1:$F$10,2,1) |
Thanks
mg