# Excel Workout 4 - Index & Match Functions

Welcome to Excel Workout #4!

Difficulty Level:

This week’s challenge is designed to test your knowledge on Index & Match Functions.

Index Function

INDEX is a function in Excel that is used to retrieve a value or a reference to a cell from a specific row and column within a range of cells or an array.

Match Function

MATCH is a function in Excel that is used to search for a value in a range of cells and return its position or index number within that range.

Goals

• Write a formula in cell V6 to find the Result based on the initial data specified in cells V4 and V5.

• Write a formula in cell V10 to find the Row Number for the specified Name, Surname (cell V4)

• Write a formula in cell V11 to find the Column Number for the specified
Department (cell V5)

• Write a formula in cell V12 to find the result for the specified Row
Number (cell V4) and Column Number (cell V5).

Submission

Period
This workout will be released on Monday April 06, 2023, and the author’s solution will be posted on Sunday April 11, 2023.

Ilgar Zarbaliyev
Challenge #4.xlsx (8.3 MB)

1 Like

Two way lookup Solution Formula with Index - Match

``````Result =INDEX(\$L\$5:\$Q\$54,MATCH(V4,\$L\$5:\$L\$54,0),MATCH(V5,\$L\$4:\$Q\$4,0))
Step By Step Solution
Row number =MATCH(V4,L5:L54,0)
Column=MATCH(V5,L4:Q4,0)

``````

This can also be achieved by other lookup functions like XLOOKUP. But I will stick to INDEX and Match. Making the data range absolute is also not necessary. With XMATCH the third argument is not necessary as it will be approximate match by default.

1 Like

@IlgarZarbaliyev Thank you the workout

Here is my result:

Summary
1 Like

Since @Chandoo has already observed that XLOOKUP is performance efficient, I will stick to the XLOOKUP.

``````=XLOOKUP(V4, L5:L54, XLOOKUP(V5, L4:Q4, L5:Q54))
``````

Other such combinations are:

``````=FILTER(FILTER(L5:Q54, L5:L54 = V4), L4:Q4 = V5)
=CHOOSECOLS(CHOOSEROWS(L5:Q54, XMATCH(V4, L5:L54)), XMATCH(V5, L4:Q4))
``````

Challenge #4.xlsx (8.3 MB)

2 Likes

There are multiple ways, but based on the post I used Index and Match formulas to get the desired results.

**
Challenge #4.xlsx (8.3 MB)
**

Write a formula in cell V6 to find the Result based on the initial data specified in cells V4 and V5.

`[spoiler]=INDEX(\$L\$4:\$Q\$54,MATCH(\$V\$4,\$L\$4:\$L\$54,0),MATCH(\$V\$5,\$L\$4:\$Q\$4,0))[/spoiler]`

Write a formula in cell V10 to find the Row Number for the specified Name, Surname (cell V4)

=MATCH(\$V\$4,\$L\$5:\$L\$54,0)

Write a formula in cell V11 to find the Column Number for the specified Department (cell V5)

=MATCH(\$V\$5,\$L\$4:\$Q\$4,0)

Write a formula in cell V12 to find the result for the specified Row Number (cell V4) and Column Number (cell V5).

`[spoiler]=INDEX(\$L\$5:\$Q\$54,V10,V11)[/spoiler]`

1 Like

HI there,
When I downloaded the file, i’m getting a different language in the cells.

Can you please make the files are in english?

thanks
Keith

Hi Keith,
A few people have already solved this challenge and no issue happened.
My files are in English.

Good luck.

I’ve only used Index & Match at an Excel Formulas and Functions course and once at work to make a Matrix for the factories.
Hover over the functions in Excel:
MATCH Returns the relative position of an item in an array that matches a specified value in a specified order
XMATCH Returns the relative position of an item in an array. By default, exact match is required
Why can’t MATCH just say: Returns the relative position of an item in an array?
In short, save a comma and a zero for each MATCH.
Challenge #4.xlsx (8.3 MB)

1 Like

My submission:

• Write a formula in cell V6 to find the Result based on the initial data specified in cells V4 and V5.

`[Challenge #4.xlsx|attachment](upload://3jtLreKdNpZVIYs3ruhd5lss0Xd.xlsx) (8.3 MB)`

• Write a formula in cell V10 to find the Row Number for the specified Name, Surname (cell V4)

`=MATCH(\$V\$4;\$L\$5:\$L\$54;0)`

• Write a formula in cell V11 to find the Column Number for the specified
Department (cell V5)

`=MATCH(\$V\$5;\$L\$4:\$Q\$4;0)`

• Write a formula in cell V12 to find the result for the specified Row
Number (cell V4) and Column Number (cell V5).

`=INDEX(\$L\$5:\$Q\$54;V10;V11)`

1 Like

Another good one.
Challenge #4.xlsx (8.3 MB)

1 Like

Thank you for participating in the Excel Challenge related to Index & Match Functions! I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to perform conditional summation in Excel.

Here is my solution to Workout #4:

We use the Index and Match functions to search for values at Column and Row intersections. Index function finds the value at the intersection of row and column numbers shown in the specified array. We can find the Row and Column numbers with the Match function.
The formulas to write cells V6, V10, V11 and V12 are as follows, respectively:

• =INDEX(L5:Q54,MATCH(V4,L5:L54,0),MATCH(V5,L4:Q4,0))

• =MATCH(V4,L5:L54,0)

• =MATCH(V5,L4:Q4,0)

• =INDEX(L5:Q54,V10,V11)

Challenge #4 with Solution.xlsx (8.3 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

Just copy the format from the row above and that will fix that.

1 Like

Just catching up on past workouts.

Cell v6 =INDEX(\$L\$4:\$Q\$54,MATCH(\$V\$4,\$L\$4:\$L\$54,0),MATCH(\$V\$5,\$L\$4:\$Q\$4,0))
cell v10 =MATCH(\$V\$4,\$L\$4:\$L\$54,0)
cell v11 =MATCH(\$V\$5,\$L\$4:\$Q\$4,0
cell v12 =INDEX(\$L\$4:\$Q\$54,V10,V11)

thanks
Keith

1 Like

That’s great.

Thank you for this challenge.

1 Like

Challenge #4_Homesh Agarwal.xlsx (8.3 MB)
The formula in V6- Using INDEX and MATCH together

=INDEX(\$L\$5:\$Q\$54,MATCH(V4,\$L\$5:\$L\$54,0),MATCH(V5,\$L\$4:\$Q\$4,0))

Row Number in V10:

=MATCH(V4,\$L\$5:\$L\$54,0)

Column Number in V11:

=MATCH(V5,\$L\$4:\$Q\$4,0)

Value using Row and Column Numbers in V12:

=INDEX(\$L\$5:\$Q\$54,V10,V11)

1 Like
``````[Challenge #4.xlsx|attachment](upload://dl8RldmgnJWyVXRGJTcpaTbTlq9.xlsx) (8.3 MB)

=INDEX(L5:Q54,MATCH(V4,L5:L54,0),MATCH(V5,L4:Q4,0))
=MATCH(L11,L4:L54, 0)
=MATCH(L11, L11:Q11, 0)
=INDEX(L4:Q54, MATCH(L11, L4:L54, 0), MATCH(L11, L11:Q11, 0))
``````
1 Like

@IlgarZarbaliyev - Thank you for the workout. It refreshed my concept which I didn’t used for a long time.

Here is my result:

Challenge #4 - Workout.xlsx (8.3 MB)

1 Like