MODE Formula based off an array using a lookup

Bl4ckPantha

New member
Joined
May 23, 2018
Messages
12
Reaction score
0
Points
0
Excel Version(s)
2016
Hi Guru's

I am trying to identify the most common value in a column based off a lookup value in a separate column.

For Example:
Formula will be inputted into "F3" using the value in "E3" to identify the most common value in column B using the ARRAY B3:C11. So in this example the lookup value is ABX and the formula should return 29.99 as it is the most frequent value in the table against ABX ("E3")

2018-06-01 12_22_09-Season Analysis - Excel.png
 
Try this Array* formula:

=MODE(IF($B$2:$B$9=E3,$A$2:$A$9))

* must be confirmed with CTRL+SHIFT+ENTER
 
Back
Top