VLOOKUP to replace excel nested if

[Mallesh23]

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.

=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

 

[Eric W]

Change your lookup table to this:

Book1
	A	B	C	D	E	F
1	Days	Ageing Bucket	Vlookup Result		Table for Vlookup
2	-100	Not Due	Not Due		-9999	Not Due
3	366	>1 Year	>1 Year		0	0-7 Days
4	8	8-15 Days	8-15 Days		8	8-15 Days
5	30	16-30 Days	16-30 Days		16	16-30 Days
6	31	31-60 Days	31-60 Days		31	31-60 Days
7	90	61-90 Days	61-90 Days		61	61-90 Days
8	91	91-120 Days	91-120 Days		91	91-120 Days
9	120	91-120 Days	91-120 Days		121	121-365 Days
10	121	121-365 Days	121-365 Days		366	>1 Year
11	365	121-365 Days	121-365 Days
12	366	>1 Year	>1 Year
Sheet4

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)

 

[Mallesh23]

Hi Eric,

Thanks a lot for your help.Awesome it worked ?


Thanks
mg

 

[Eric W]

Glad to help! Thanks for the update.