If Error formula

[gill123]

Hi,

Can any one please explain me the following formula. I do understand the vlookup part. It's looking up data in my other sheet but the iferror and left, right, i have no idea what that is saying.

=IFERROR(VLOOKUP($B130,Quarterly!$A$75:$Z$145,(LEFT(AT$129,4)-2008)*4-(7-RIGHT(AT$129)),FALSE),0)

Thank you

[nigelbloomy]

The iferror just says that if the whole formula in the middle gives you an error, then show a 0.

The vlookup is looking up the value in B130 and searching for it in column A of the Quarterly sheet. Once it finds it, the vlookup needs to know which column to look at so it can bring back that matching value.

Left looks at the left 4 digits of AT129. Multiplying it by 4, then minus 7 less than the rightmost value in AT129. It uses that formula to figure out which column to look at.

That's a pretty confusing way to figure out whihc column to get, but that's what it is doing.

[6StringJazzer]

=IFERROR(VLOOKUP($B130,Quarterly!$A$75:$Z$145,(LEFT(AT$129,4)-2008)*4-(7-RIGHT(AT$129)),FALSE),0)

says that first it evaluates this part

VLOOKUP($B130,Quarterly!$A$75:$Z$145,(LEFT(AT$129,4)-2008)*4-(7-RIGHT(AT$129)),FALSE)

and if there is no error, IFERROR returns the value that is returned by VLOOKUP. If that VLOOKUP returns an error, then IFERROR will return the 0 (in red above)

LEFT(AT$129,4)

returns the 4 leftmost characters of the string in AT$129

RIGHT(AT$129)

returns the 1 rightmost character in the string AT$129 (if the second argument is missing, it is assumed to be 1)

[gak67]

LEFT(AT$129,4) finds the first 4 characters from the left of whatever is in cell AT129. Given that it then deducts off 2008, my guess is that AT129 holds a date, and it's calculating the number of years between the year of the date in that cell and 2008.

RIGHT(AT$129) finds the right most character of whatever is in cell AT129.

IFERROR is matched with the ,0) at the end of the formula, and it returns a 0 if the rest of the formula would return an error.

[Jonmo1]

The IFERROR is easy.
If the result of the Vlookup is an error (like #N/A), then it returns 0
=IFERROR(VLOOKUP($B130,Quarterly!$A$75:$Z$145,(LEFT(AT$129,4)-2008)*4-(7-RIGHT(AT$129)),FALSE),0)

Then the basic vlookup is
=VLOOKUP($B130,Quarterly!$A$75:$Z$145,(LEFT(AT$129,4)-2008)*4-(7-RIGHT(AT$129)),FALSE)

The left and right bits seem to be being used to determine the Column Index # of the vlookup (the 3rd part of a vlookup)
What's in AT129 ?

LEFT(AT$129,4)
returns the 4 leftmost characters of the string in AT$129

RIGHT(AT$129)
returns the 1 rightmost character in the string AT$129 (if the second argument is missing, it is assumed to be 1)

Let's say AT129 is 20155

Then you have
(LEFT(AT$129,4)-2008)*4-(7-RIGHT(AT$129))
(2015-2008)*4-(7-5)
(7)*4-(2)
28-(2)
26

So the 3rd argument of the vlookup becomes 26

[gill123]

thank you all.

Step by step process makes it so much easy