CODEWITHSUNDEEP
excelexcel-formula
Adrian Devder
Adrian Devder

Reputation: 167

Finding the MODE in Excel with VLOOKUP?

I have a question about finding the MODE in excel. The table below shows 4 different income brackets and then how many families are in each bracket

I wanted to ask if its possible to find the MODE of the data set bellow without having to write it down like i did on the right side of the table?

I tried using MAX to find the largest number of families in a single income bracket and then using VLOOKUP to find the corresponding value in the other column, but for some reason it does not work.

enter image description here

Upvotes: 0

Views: 1306

Answers (1)

AnilGoyal
AnilGoyal

Reputation: 26238

Use this formula instead

=VLOOKUP(MAX(B23:B26),CHOOSE({1,2},B23:B26,A23:A26),2,0)

Actually, one of the vlookup()'s limitation is to read the array from left to right only. In your problem, you have lookup values on the right side and values to return on the left. CHOOSE function here does the job for reversing the data/array for you.

GOOD LUCK

Upvotes: 1

Related Questions