# VLOOKUP to replace excel nested if

#### Mallesh23

##### Well-known Member
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.

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
ABCDEF
1DaysAgeing BucketVlookup ResultTable for Vlookup
2-100Not Due#N/A0Not Due
3366>1 Year121-365 Days70-7 Days
488-15 Days0-7 Days158-15 Days
53016-30 Days16-30 Days3016-30 Days
63131-60 Days16-30 Days6031-60 Days
79061-90 Days61-90 Days9061-90 Days
89191-120 Days61-90 Days12091-120 Days
912091-120 Days91-120 Days365121-365 Days
10121121-365 Days91-120 Dayselse>1 Year
11365121-365 Days121-365 Days
12366>1 Year121-365 Days
Sheet1
Cell Formulas
RangeFormula
B2:B12B2=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:C12C2=VLOOKUP(A2,\$E\$1:\$F\$10,2,1)

Thanks
mg

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Eric W

##### MrExcel MVP
Change your lookup table to this:

Book1
ABCDEF
1DaysAgeing BucketVlookup ResultTable for Vlookup
2-100Not DueNot Due-9999Not Due
3366>1 Year>1 Year00-7 Days
488-15 Days8-15 Days88-15 Days
53016-30 Days16-30 Days1616-30 Days
63131-60 Days31-60 Days3131-60 Days
79061-90 Days61-90 Days6161-90 Days
89191-120 Days91-120 Days9191-120 Days
912091-120 Days91-120 Days121121-365 Days
10121121-365 Days121-365 Days366>1 Year
11365121-365 Days121-365 Days
12366>1 Year>1 Year
Sheet4
Cell Formulas
RangeFormula
B2:B12B2=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:C12C2=VLOOKUP(A2,\$E\$1:\$F\$10,2,1)

#### Mallesh23

##### Well-known Member
Hi Eric,

Thanks a lot for your help.Awesome it worked ?

Thanks
mg

#### Eric W

##### MrExcel MVP
Glad to help! Thanks for the update.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,286
Messages
5,836,425
Members
430,428
Latest member
Deepak Tanwar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back