# 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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### 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.

Replies
7
Views
88
Replies
4
Views
149
Replies
4
Views
268
Replies
1
Views
257
Replies
1
Views
98

1,130,205
Messages
5,640,835
Members
417,171
Latest member
Mr___D

### 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