You can use the following syntax to use the INDEX and MATCH functions across multiple columns in Excel:
=INDEX($A$2:$A$5,MATCH(1,MMULT(--($B$2:$D$5=F2),TRANSPOSE(COLUMN($B$2:$D$5)^0)),0))
This particular formula will look up the value in cell F2 in the range B2:D5 and then return the corresponding value in the range A2:A5.
The following example shows how to use this syntax in practice.
Example: How to Use INDEX MATCH Across Multiple Columns in Excel
Suppose we have the following dataset in Excel that shows the names of basketball players in various positions on four different teams:
Now suppose we create another column that contains the name of each player:
Now suppose we would like to look up the name of each player and return the name of their team.
We can type the following formula into cell G2 to do so:
=INDEX($A$2:$A$5,MATCH(1,MMULT(--($B$2:$D$5=F2),TRANSPOSE(COLUMN($B$2:$D$5)^0)),0))
We can then click and drag this formula down to each remaining cell in column G:
Column G returns the name of the team that corresponds to each player name in column F.
For example:
- The formula looks up Andy in the range B2:D5 and returns Mavs.
- The formula looks up Bob in the range B2:D5 and returns Warriors.
- The formula looks up Chad in the range B2:D5 and returns Kings.
And so on.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use INDEX and MATCH to Return Multiple Values Vertically
Excel: How to Use INDEX MATCH if Cell Contains Text
Excel: How to Use INDEX MATCH with 3 Criteria
=INDEX(A2:A21,MATCH(MAX(F2:Q21),A2:A21,0)) shows #N/A
column a = Name
Column F to q Fish caught.
I want to get the biggest fish listed ( F2:Q21) and place the name of the angler listed for the big fish into a37
I think i’m close but it just not working .. can you help
Hi James…It looks like you are close, but the issue arises because `MATCH` is not designed to work across multiple columns the way you’re using it. Here’s a step-by-step guide on how to achieve what you need:
### Problem Breakdown:
– **Column A** contains the names of the anglers.
– **Columns F to Q** contain the fish caught by each angler.
– You want to find the largest fish in **F2:Q21** and return the name of the angler in **A2:A21** corresponding to the largest fish.
### Solution:
1. **Find the Largest Fish**: First, we need to find the largest value in the range **F2:Q21**.
2. **Find the Row of the Largest Fish**: Then, we need to find out which row contains this largest value.
3. **Return the Angler’s Name**: Finally, we’ll use this row number to retrieve the angler’s name from **Column A**.
### Formula:
1. **Find the Largest Fish (Helper Column – Optional)**:
To find the largest fish caught in the range **F2:Q21**, you can use the following formula in **A37**:
“`excel
=MAX(F2:Q21)
“`
This gives you the largest fish caught.
2. **Find the Angler’s Name** (Combined Formula):
Use this formula to find the angler who caught the biggest fish and place their name in **A37**:
“`excel
=INDEX(A2:A21, MATCH(MAX(F2:Q21), INDEX(F2:Q21, MATCH(MAX(F2:Q21), F2:F21, 0), 0), 0))
“`
This formula does the following:
– `MAX(F2:Q21)` finds the largest fish.
– `MATCH(MAX(F2:Q21), F2:F21, 0)` finds the row in the first column that contains the largest value.
– `INDEX(A2:A21, …)` retrieves the name from column **A** based on the row number returned by `MATCH`.
This should solve the issue and return the angler who caught the biggest fish into **A37**.
Let me know if it works!
First THNAK YOU for your Help, I;ve tried for a week to get this right before asking for help. it did show a name but.. the wrong one.
John , Jim, Bill (a2:a4)
fish 1 and 2
44 ,34 ,32 (john)
44.25 ,46 ,32.5 (jim)
30 ,29 ,40.75 (bill)
I also changed the last zero too 1 or -1 it did not show Jim as the biggest
( , F2:F21, 0), 0), 0))
Jim has the biggest at 44.25 but Bill name show up
I can send you a copy of the sheet if you tell me your email or how to post it to you?
thanks again for your help
Hi Jim…Please clarify your question so that we may better assist you.
Column A 2 to 21 Names of fisherman
Column F,H,J,L,N,P our for Fish during this tournament
Column G,I,K,M,O,Q our for Time the fish are caught
I already have Column D total weight for each fisherman and E total fish count working
also have total length for all and total caught
Also have 1st,2nd and 3rd place working with names and total length
** Column A 33 code: =INDEX($A$2:$A$26,MATCH(D33,$D$2:$D$26,FALSE),1) —- Name
** Column D 33 Code : =INDEX($A$2:$A$26,MATCH(D33,$D$2:$D$26,FALSE),1) —- length
here is my issue largest fish Column D37 works — =MAX(F2:Q21)
but i can’t get the name of the largest fish to show in A37
Fish 1 F, Fish 2 H. etc.. J,L,N,P
Time 1 G,time2 I . etc K,M,O,Q
A 2 to A21 Names
Hope this helps
Column A 2 to 21 Names of fisherman
Column F,H,J,L,N,P Fish during this tournament
Column G,I,K,M,O,Q Time the fish are caught
Fish and Time must be side by side.. I do this in a boat over my phone in excel.
I already have Column D total weight for each fisherman and E total fish count working
also have total lenght for all and total caught
Also have 1st,2nd and 3rd place working with names and total lenght
** Column A 33 code: =INDEX($A$2:$A$26,MATCH(D33,$D$2:$D$26,FALSE),1) —- Name
** Column D 33 Code : =INDEX($A$2:$A$26,MATCH(D33,$D$2:$D$26,FALSE),1) —- lenght
here is my issue largest fish Column D37 works — =MAX(F2:Q21)
but i can’t get the name of the largest fish to show in A37
Thank you for your help I will try to make this work.. I enjoy ms access using vba.. this is my 1st try with excel self taught
have a great day
You are very welcome Jim! We appreciate your feedback!