vlookup from a age range column and get the value from column 2

[jr7138]

Team:

I have a cell where I enter an age (1). When I enter my vlookup formula, it will not work due to the age column (A) being in a age range (40-45). Of course it works if I have a single age, say '40'. Does anyone have a simple solution? Please see my screen shot below:

1. Enter the age
2. Return Value =VLOOKUP(C14,F2:G11,2,TRUE) to return the value from Column "B"

A. Age Range column
B. Value to return

Thanks,
jer

 

[RossTattersall]

RossTattersall:

Excellent formula. Worked like a charm, had to change the under 30 to under 29, because if I enter 30 it would add those two amounts from column 2.

Can you please explain the formula, like I'm a five-year old.

Thanks for you help.

Regards,
jer

It would be an impressive 5 year old who understands this!

So Sumproduct is a relatively special function because it can handle arrays inside itself, arrays being basically lists of things. It then multiplies the first thing from each list together, the second thing etc and sums the results of those multiplications. This formula basically sets up 1's and 0's so that only the desired thing gets picked up;

--(SUBSTITUTE(RIGHT(F2:F11,2),"ve",10000)*1>=C14)

RIGHT(F2:F11,2) takes the right 2 elements of each string (e.g the last number; we'd have to be a bit fancier if numbers weren't all 2 digits).
SUBSTITUTE(RIGHT(F2:F11,2),"ve",10000) swaps any instance of "ve" (e.g to catch the 75 and above) and replaces it with the number 10000, so the age will always be below that number. If you're handling anyone older than 10,000 years old you'll need to update this.
The *1 at the end of the substitute turns the text into numbers, because the string "40 - 44" is not a number, so Excel interprets the last 2 digits of it as also not being a number, unless we force it to.

So now we have a list of the right 2 characters of each of the strings in the first column, with any instance of "ve" swapped for 10,000, all converted into numbers. In other words;

{30;34;39;44;49;54;59;64;69;10000}

The ; is just because they're columns, whereas a , would signify a row.

(SUBSTITUTE(RIGHT(F2:F11,2),"ve",10000)*1>=C14) then checks what in this list is greater than C14 and returns TRUE or FALSE, so if C14 was 40 we'd have;

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

So you can see it's false for the first 3 elements (30, 34, 39) and true for the rest.

But Sumproduct wants 1's and 0's rather than TRUE and FALSE, that's what the "--" at the start does. It swaps the TRUE for 1 and FALSE for 0. This gives;

{0;0;0;1;1;1;1;1;1;1}

Next bit;

--(SUBSTITUTE(LEFT(F2:F11,2),"Un",0)*1<=C14)

This is the same thing as before, but swapping "un" for 0 to catch the "Under 30" part, and looking for what's less than C14 instead. If HR ever insist on changing the wording, you'll need to amend this part (for example "Below 30" would require you to swap "Un" for "Be". It isn't case sensitive either). This resolves to;

{1;1;1;1;0;0;0;0;0;0}

You can see that the only element which is 1 in both lists is the 4th one, which relates to the 40 - 44 range, just as we wanted. The final sumproduct is;

=SUMPRODUCT({0;0;0;1;1;1;1;1;1;1},{1;1;1;1;0;0;0;0;0;0},{0.07;0.09;0.11;0.15;0.22;0.32;0.5;0.82;1.33;2.25})

So; 0 * 1 * 0.07 + 0 * 1 * 0.09 + 0 * 1 * 0.11 + 1 * 1 * 0.15 + 1 * 0 * 0.22 + 1 * 0 * 0.32 + 1 * 0 * 0.5 + 1 * 0 * 0.82 + 1 * 0 *1.33 + 1 * 0 * 2.25

Giving the desired results.

Hope this helps!

 

[jr7138]

It would be an impressive 5 year old who understands this!

So Sumproduct is a relatively special function because it can handle arrays inside itself, arrays being basically lists of things. It then multiplies the first thing from each list together, the second thing etc and sums the results of those multiplications. This formula basically sets up 1's and 0's so that only the desired thing gets picked up;

--(SUBSTITUTE(RIGHT(F2:F11,2),"ve",10000)*1>=C14)

RIGHT(F2:F11,2) takes the right 2 elements of each string (e.g the last number; we'd have to be a bit fancier if numbers weren't all 2 digits).
SUBSTITUTE(RIGHT(F2:F11,2),"ve",10000) swaps any instance of "ve" (e.g to catch the 75 and above) and replaces it with the number 10000, so the age will always be below that number. If you're handling anyone older than 10,000 years old you'll need to update this.
The *1 at the end of the substitute turns the text into numbers, because the string "40 - 44" is not a number, so Excel interprets the last 2 digits of it as also not being a number, unless we force it to.

So now we have a list of the right 2 characters of each of the strings in the first column, with any instance of "ve" swapped for 10,000, all converted into numbers. In other words;

{30;34;39;44;49;54;59;64;69;10000}

The ; is just because they're columns, whereas a , would signify a row.

(SUBSTITUTE(RIGHT(F2:F11,2),"ve",10000)*1>=C14) then checks what in this list is greater than C14 and returns TRUE or FALSE, so if C14 was 40 we'd have;

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

So you can see it's false for the first 3 elements (30, 34, 39) and true for the rest.

But Sumproduct wants 1's and 0's rather than TRUE and FALSE, that's what the "--" at the start does. It swaps the TRUE for 1 and FALSE for 0. This gives;

{0;0;0;1;1;1;1;1;1;1}

Next bit;

--(SUBSTITUTE(LEFT(F2:F11,2),"Un",0)*1<=C14)

This is the same thing as before, but swapping "un" for 0 to catch the "Under 30" part, and looking for what's less than C14 instead. If HR ever insist on changing the wording, you'll need to amend this part (for example "Below 30" would require you to swap "Un" for "Be". It isn't case sensitive either). This resolves to;

{1;1;1;1;0;0;0;0;0;0}

You can see that the only element which is 1 in both lists is the 4th one, which relates to the 40 - 44 range, just as we wanted. The final sumproduct is;

=SUMPRODUCT({0;0;0;1;1;1;1;1;1;1},{1;1;1;1;0;0;0;0;0;0},{0.07;0.09;0.11;0.15;0.22;0.32;0.5;0.82;1.33;2.25})

So; 0 * 1 * 0.07 + 0 * 1 * 0.09 + 0 * 1 * 0.11 + 1 * 1 * 0.15 + 1 * 0 * 0.22 + 1 * 0 * 0.32 + 1 * 0 * 0.5 + 1 * 0 * 0.82 + 1 * 0 *1.33 + 1 * 0 * 2.25

Giving the desired results.

Hope this helps!

Excellent! Thank you so much for the detailed outlined. I am going to save and copy to a job-aid, so in months when I encounter this again I can reference your notes.

Thanks so much.
JER

 

[RossTattersall]

Happy I could help!