[addtoany]

Using Excel Help from the Internet – Understanding Complicated Functions

This article shows how you can adapt and use Excel help from the internet so that it works in your own Excel document. When you get stuck with a specific problem in Excel, you can often find a solution by searching for Excel help on the internet. But when you find a solution on the internet, it is often not obvious how to adapt it to make it work in your own document. This article guides you, step by step, through adapting functions you have found on the internet so that they work in your own Excel document.

I will use the example where someone is searching for how to do a lookup (VLOOKUP) based on several columns and finds the following solution:

{=INDEX($G$5:$G$1082,MATCH($K$5&$K$6,$C$5:$C$1082&$B$5:$B$1082,0))}

Translate the function names to your Excel language. If you use Excel in English, you do not need to do anything with the function above, but if you use Excel in Swedish you need to translate it to:

{=INDEX($G$5:$G$1082,PASSA($K$5&$K$6,$C$5:$C$1082&$B$5:$B$1082,0))}

Translate the characters in the function to your local language. If you use Excel in English, you do not need to do anything with the function above, but if you use Excel in Swedish you need to replace ”,” with ”;” because the Swedish version uses ”,” as the decimal separator and ”;” as the separator between parameters in functions. We then get:

{=INDEX($G$2:$G$10;PASSA($K$5&$K$6,$C$2:$C$10&$B$2:$B$10;0))}

Now for the difficult step. You need to understand what the references (in this case $G$2:$G$10, $K$5, $K$6, $C$2:$C$10 and $B$2:$B$10) stand for and replace them with the corresponding references in your own document.

In the example, $G$2:$G$10 is the range in which you search for a value. $K$5 and $K$6 are the search criteria you want to be met. $C$2:$C$10 and $B$2:$B$10 are lists that contain the criteria you are searching on. An adaptation could, for example, look like this:

{=INDEX(forsaljningKolumn;PASSA(land&produktgrupp,landKolumn&produktgruppKolumn;0))}

If the function is enclosed in {…}, as the function above is, it means you have an array formula. In that case, remove { and }. Then press SHIFT+CONTROL+ENTER instead of ENTER to confirm the function. By doing so, you tell Excel that you want to enter the function as an array formula. Excel then adds the { and } symbols itself.

With this guide you can adapt most functions you find on the internet to your own document, even if the functions are so difficult that you could not have written them yourself from scratch. Good luck!