Using VLOOKUP instead of Nested IF with Negative values

[Webbers]

I have taken over an existing workbook, and have been trying to clean it up a bit. One of the things I wanted to fix was a nested IF statement (there are actually multiple). The original formula is:

Please Login or Register  to view this content.

I simply cannot seem to make it work. Now I have used this before, however there were never negative values involved like this. Column U displays the OLD formula and results, which are all correct. And column V contains my attempt at converting this formula to a VLOOKUP. The table for the named range is on the codes sheet. Thanks!!!

[AliGW]

Put the lookup table in ascending order - you have it in descending order:

Excel 2016 (Windows) 32 bit

	A	B
1	variable	Aging Results
2	-5000	More than a year
3	-325	6 Months to 1 Year
4	-165	3 Months to 6 Months
5	-75	30 Days to 90 Days
6	15	Less than 30 days

Sheet: codes

and then use:

=VLOOKUP($Q2,AgingResults,2,TRUE)

It's an approximate match.

[Webbers]

Thanks AliGW,

Worked like a charm, and such a simple mistake I made.