How to use index match in excel between two sheets

Excel's VLOOKUP tool is useful for locating values, but it has several restrictions. Instead, you can seek up values in any place or direction in your spreadsheet by using a combination of the INDEX and MATCH functions. In contrast to MATCH, which performs the opposite and returns a location based on the value you enter, the INDEX function returns a value based on a location you enter in the formula. You can locate any number or text you require by combining these features.

Vlookup VS Index and Match in excel online, 2016 and 2019:

These routines search for values from the left to the right, whereas VLOOKUP searches from the right to the left. The function, VLOOKUP, does a vertical lookup, hence the name. WPS continues by stating that INDEX and MATCH can be used as an alternative if your sheet is not organized in a way that VLOOKUP can assist you in finding what you need. So let's examine how to use Excel's INDEX and MATCH functions.

Index and Match functions basics in excel:

1.It's crucial to comprehend the function's goal and organization before combining it with other functions. In Array Form, the syntax for INDEX is INDEX(array, row number, column number), with the third argument being optional. An index position is looked up, and the value is returned. The formula below should be entered to determine the value in the fourth row in cells D2 through D8, for example:

2.Since the number is in the fourth position in our cell range, the answer is 20,745

MATCH has the following syntax: MATCH(value, array, match type), with the third argument being optional.

MATCH looks up a value and returns the location of that value. You would enter the following formula to determine the value in cell G2 in the range A2 through A8:

3.Since cell G2's value is at the fourth spot in our cell range, the result is 4.

How to use Index and Match in excel:

It's time to put this dynamic pair to use now that you understand what each function performs and how to use it. The same information from before will be used below for INDEX and MATCH separately. Instead of using the position to look up, you will substitute the MATCH function formula inside the INDEX function formula.

1.You would use the following formula to determine the value (sales) based on the location ID:

=INDEX(D2:D8,MATCH(G2,A2:A8))

2.It comes to 20,745. Cell G2 in the range A2 through A8 is where MATCH detects the value, which it then sends to INDEX, which then searches cells D2 through D8 for the answer.

Note: This above written article is an attempt to show you how to use index match in excel online, 2016 and 2019, in both windows and mac.

You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.