No subject was specified.

[Ronald Cayne]

What is the best approach to completing col xx. vlookup? nested if's.
Column X represents no. of days. column xx represents the aged grouping.

I

TABLE col x col xx
0 "< 30 days" 154 "151-180 da"
30 "31-60 da" 139 "121-150 da"
60 " "61-90 da" 164
90 "91-120 da" 189
120 "121-150 da" 1825
150 "151-180 da" 199
180 "181-365 da" 236
365 "1-2 yrs" 120
730 "2-3 yrs" 65
1095 "3-5 yrs" 299
1825 ">5 yrs" 284
731
23
359
384
369
555
181

 

[Guest]

Hi,
The essence of the Vlookup is how you set up the lookup value and seeting
the Lookup to True. You will have to adjust for the Column differences, but
you need something like this:
col x col xx
0 < 30 days 154 151-180 da
30 31-60 da 139 121-150 da
60 61-90 da 164 151-180 da
90 91-120 da 189 181-365 da
120 121-150 da 1825 >5 yrs
150 151-180 da 199 181-365 da
180 181-365 da 236 181-365 da
365 1-2 yrs 120 121-150 da
730 2-3 yrs 65 61-90 da
1095 3-5 yrs 299 181-365 da
1825 >5 yrs 284 181-365 da
=VLOOKUP(C16,$A$16:$B$26,2,TRUE) <-----This is the formula you need in the
lookup. The number in the first column is a number equal to number of days,
there is a little tweeking to be done, but I am sure you will be able to do
that.
Thanks,

 

[Dave Peterson]

I think I'd use a table like:

0 < 30 days
31 31-60 days
61 61-90 days
91 91-120 days
121 121-150 days
151 151-180 days
181 181-365 days
366 1-2 yrs
731 2-3 yrs
1461 3-5 yrs
1826 >5 yrs

then just use =vlookup() formula like:
=VLOOKUP(x1,A1:B11,2)