Index and Match Function is an alternative method from VLOOKUP. For this tutorial, I have created an Excel Sheet of Steve's Laptop Store Sale Result. The laptops include Apple, HP, Dell, Lenovo and Asus. We will also show you how to convert your INDEX and MATCH function into an API at the end of the video. Stay Tune.
Download: AlchemyJ FREE Community Edition: https://www.alchemyj.io/download
Learn More: How to setup AlchemyJ: https://www.youtube.com/watch?v=eTyYTf1sw9k&feature=youtu.be&ab_channel=AlchemyJ
How AlchemyJ works: https://www.youtube.com/watch?v=bAQHo8I1xuQ&ab_channel=AlchemyJ
Let's connect: LinkedIn: https://www.linkedin.com/company/alchemyj/
Article
Index and Match Function is an alternative method from VLOOKUP. For this tutorial, I have created an Excel Sheet of Steve's Laptop Store Sale Result. The laptops include Apple, HP, Dell, Lenovo and Asus. Without further ado, let's get started.
How to use INDEX and MATCH Function
First, we will need an Index Function. Let us create our Index. Select on the cell F19 and type in the equal, INDEX, open parentheses. Select Cell C6 and drag your cursor to G17. In this table, row 6 and column C will be representing 1. Type in comma, 1, comma and 1. Close parentheses and hit the return key. You will have an output of $36244, which are the sales of Apple Brand Laptop in January.
Second, we will need 2 Match Function. One to match the Month and the other to match the Brand of the laptops. Now let us create our Month Match example. Select on Cell F20, type in the equal, match, open parentheses. Select Cell C20 and type in the comma. Select Cell B6 and drag your cursor to B17. Type in comma and Zero. Zero represent an exact match. Close parentheses and hit the return key. You will get NA. It is because our Month Cell C20 is empty. Let us add in January. You will get 1.
Next, let us create our Laptop Match example. Select on Cell F21, type in the equal, match, open parentheses. Select Cell C21 and type in the Comma. Select Cell C5 and drag your cursor to G5. Type in Comma and Zero. Close parentheses and hit the return key. As usual, you will get NA. Let us add Apple in Cell C21. You will get 1.
Lastly, we will be putting together the Index and Match function to lookup for the sales of Apple in January. Select on the cell C19 and type in the equal, INDEX, open parentheses. Select Cell C6 and drag your cursor to G17. Type in Comma and input the Match – Month formula. Type in Comma and input Match – Laptop formula. Close parentheses and hit the return key. You will get $36,244, Apple’s January sales.
Intermission
There you have it, the INDEX and MATCH function for Excel. If you have any excel formula you want us to teach, let us know in the comment section below. Don't forget to subscribe, hit the bell button and turn on the notification to have more free excel function tutorial. But, before you go, we have a bonus clip for you. I will be turning this Excel spreadsheet into an API. Stay tuned to see how the magic works.
Convert INDEX and MATCH function into Application Programming Interface (API)
I will be using AlchemyJ Studio Excel Add-in to turn this INDEX and MATCH Function Excel into an API. To do this, I will need AlchemyJ Excel installed and with a pre-set of rules. I will select the AlchemyJ ribbon, select Generate API and click on Generate API. The file is now converting. Okay, your API is ready.
I will now launch this openAPI that AlchemyJ generates and try using the INDEX and MATCH Function with our Dell and January example. I will expand this tab, and select try it out. I will add in December for the month and Dell for the laptop. So, will we get $4,734 after I select the execute button? Yes, we got it.
Closing
AlchemyJ Studio Excel Add-in turns your Excel business model into API. This way, you can integrate your Excel model into other applications. You can download AlchemyJ Studio Excel Add-in for free at alchemyj.io. And once again, thank you for watching. So which Excel function you wish to be converted into an API? Let us know in the comment section below. Lastly, don't forget to subscribe, hit the bell button and turn on the notification. See you soon.