Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No subject was specified.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
No subject was specified.
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, "Ronald Cayne" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
No subject was specified.
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) Ronald Cayne wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No Subject | Excel Discussion (Misc queries) | |||
No Subject | Excel Discussion (Misc queries) | |||
No Subject | Excel Discussion (Misc queries) | |||
No Subject | Excel Discussion (Misc queries) | |||
No Subject | Charts and Charting in Excel |