Dont want to step on any toes so i hope Mike doesnt mind me asnwering Mahir's questions
First, i want to say that i had to go to the Formula ribbon and set the Calculations options to automatic to see the formulas update/work correctly.
Mahir:
since you're only asking about the IFERROR porition, i'll assume you understand the rest of the formula?
If you look at the formula and ignore the IFERROR: INDEX($F$2:$F$21,MATCH(A2,$G$2:$G$21,0))
BUT FOR THOSE WHO DON'T>>>>>
the INDEX function returns a value from the provided array and row #. In this case the array that has the desired values that we want return are in F2:f21.
So if we used this: INDEX($F$2:$F$21,2) it would look at the F2:F21 array, and return the value in the second row..."Bradesco FI Multimercado Dynamic"
but the Row# will need to change. So we use the Match Function:
MATCH(A2,$G$2:$G$21)
This function by itself would look at the value in A2 with in the range G2:G21 and return the Row# that the vaule of A2 was found.
So if A2="SDA Hedge" this function would simply return 16....because that value is located in the 16th row of G2:G21.
So... now on to the IFERROR part. IFERROR(value,value_if_error)
this is a really starightfoward function. It looks at a value/cell and if that cell results in an error, it will return another value. the way Mike used this function, and technically how it is intended to be used... is all cautionary. you see... if you took the IFERROR part out, you'd get the same results as you do with it in! thats because none of the nested formulas result in an error, so therefore, it never executes the Value_if_Error argument. But if you'd like to test this out... all you have to do is simply type in any value in one of the cells in A2:A21... like youre name.
Now we will see how the IFERROR works and what it does/how it can be used.
First, lets do this... lets see what would happen if we DIDNT use the IFERROR function.
In B4, edit the formula mike provided to: INDEX($F$2:$F$21,MATCH(A2,$G$2:$G$21,0))
Now, go to cell A4 and type in your name. the result in B4 should be the #N/A error!! thats because it couldnt find your name in the ranges that we told it to look.
The IFERROR fucntion allows us to do something in the event that our formula errors out/can't return anything. instead of seeing any of the Errors like we just got in B4, we can say... if this happens, do this instead.
In Mikes formula, he is basically saying that if the INDEX/MATCH formula doesnt work; if you can't find the value in Col. A, return "" (a blank cell).
So lets check this out! change the formula in B4 again to: =IFERROR(INDEX($F$2:$F$21,MATCH(A2,$G$2:$G$21,0)),""). The results should not just be a blank/empty cell. "" is used in this instance a lot of the times when other calculations/values are not needed but we just dont want to see those annoying Error codes in the cells (cleans up the spreadsheet a little).
now try this formula in B4" =IFERROR(INDEX($F$2:$F$21,MATCH(A2,$G$2:$G$21,0)),"Name not found")
We're trying to find the names in col. A but there could be a chance where one is missing or spelled wrong or could have an extra space. IF this happens, we can also use the Value_if_Error argument to display a message (like the formula we just used above "Name not found". OR you coudl have it do any combination of formulas/lookups/etc.
I hope this wasn't too much information or too confusing. I'd be glad to explain any parts if needed!!
