The INDEX-MATCH formula cannot only look up values to the left and right of your data but its versatility means that it can also lookup the largest values within an array.
Say that you had a list of Sales Reps in one array and their corresponding Sales Values in another array.
By nesting a MAX function, you can tell the formula to find the biggest Sales Value and return the Sales Rep corresponding to that.
Here is how….
DOWNLOAD WORKBOOK
Hi,
I trying to find the max value and return the same value with same dealer
like
This is my table
retailer jan feb
aaa comm 30 20
agarwal 67 60
sony tel 85 70
goel & sons 35 20
planet world 87 90
goel & sons 30 40
agarwal 50 75
and i want this result
retailer jan feb
goel & sons 35 40
agarwal 67 75
Please help me to solve this.
Hi Prem,
You can use a pivot table, then:
1. On Rows, place the retailer there,
2. Then on values, place Jan and Feb there
3. Then change both settings to be Max
It should give you your expected result.
Regards,
Bryan
Hi, I am trying to do something similar but i was wanting to display more than one match in the same cell. For example, I have a speadsheet which has 4 columns 1st, 2nd, Equal & /-. The row beneath it has a formula which totals the number of times each of those headings is selected in a separate worksheet. It looks like:
1st 2nd Equal /-
2 0 2 1
What I’m trying to do is get a 5th column to show which of the headings is selected the most. I have used the below formula so far:
=INDEX($J$6:$M$6,0,MATCH(MAX($J8:$M8),$J8:$M8,0)) and it works perfectly. Problem is that the totals are a running tally & on some occasions they may occur the same amount of times. I can not figure out how to adjust my formula so that it will display all that are most often selected (eg – 1, Equal) in the same cell.
Is this something you could possibly help me with? Please?
Thank you in advance!
Hello Alana,
Yes I can easily help you out 🙂
Please email me your sample workbook so I can check the formula and come to a solution.
Please send to john@myexcelonline.com
Cheers,
John
=INDEX(H:H,MATCH(1,INDEX((R$2=G$2:G$25000)*(BH$1=F:F),0),0))
this works but brings back the first instance found , I need to find the max and or min value.
can you help?
Hello Jeff,
You will need to nest the MAX formula =INDEX(H:H,MATCH(MAX(H:H),INDEX((R$2=G$2:G$25000)*(BH$1=F:F),0),0))
Can you please send me your workbook so I can have a look at the formula references.
This way I will be able to come to an answer.
Thanks,
John
Plsss help me, how can I lookup or index match Apple3, Orange4 & Banana3 value-
Apple1 80.00
Apple2 100.00
Apple3 90.00
Orange1 120.00
Orange2 220.00
Orange3 180.00
Orange4 160.00
Banana1 250.00
Banana2 330.00
Banana3 245.00
Hi Faruqur,
A vlookup will be a better fit for your problem:
https://www.myexcelonline.com/blog/vlookup-function-introduction-excel/
Regards,
Bryan
This is a very useful combination of formulas to know. Every since being able to use INDEX MATCH when needed over VLOOKUP, work has been more fun.
Hey Jimmy,
Yes INDEX-MATCH combination is more versatile than VLOOKUP. There are heaps of different ways you can use it.
Thanks,
John
