VLOOKUP to replace excel nested if

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,825
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
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
Hi Eric,

Thanks a lot for your help.Awesome it worked (y) 🕺


Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top