Returning a minmum value from a vlookup

anthonyyo

New Member
Joined
Mar 30, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Ok, I can't seem to find the answer to this anywhere.

I have a table of data, specifically from google search console

Let's say it looks like this:

QueryPageClicksImpressionsCTRPosition
keywordMrExcel
6,833​
160,725​
4.25%​
4.2​
keywordhttps://www.website.com/subpage/
6,236​
43,563​
14.31%​
2.1​
keyword twohttps://www.website.com/subpage/
2,844​
24,975​
11.39%​
3.2​
keyword twohttps://www.website.com/subpage/subpage
2,303​
20,957​
10.99%​
2.4​

What I need help with is a vlookup (or index it seem may be an option) that if I get a match on Query it will return the lowest position on that query match AND it will return the page associated with the lowest position

So In my table it should be

Col ACol BCol C
keyword (the lookup value)formula for returning lowest positionformula for returning the page associated with lowest position

And the result, based off the data my first table would look like:

QueryPositionPage
keyword
2.1​
https://www.website.com/subpage/
keyword two
2.4​
https://www.website.com/subpage/subpage

I wouldn't even mind using a pivot to get that information FIRST and then doing the vlookup off that table, but I can;t seem to figure out how to show only one result of page based off the min value of query.

ANY help here would be greatly appreciated.
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Something like this would work.
Since you have 365, you can use MINIF rather then my CSE MIN/IF:

Book3.xlsx
ABCDEF
1QueryPageClicksImpressionsCTRPosition
2keywordCreate Your Free Website | Free Website Builder | Website.com6,833160,7254.25%4.2
3keywordhttps://www.website.com/subpage/6,23643,56314.31%2.1
4keyword twohttps://www.website.com/subpage/2,84424,97511.39%3.2
5keyword twohttps://www.website.com/subpage/subpage2,30320,95710.99%2.4
6
7
8
9
10
11QueryPositionPage
12keyword2.1https://www.website.com/subpage/
13keyword two2.4https://www.website.com/subpage/subpage
Sheet878
Cell Formulas
RangeFormula
B12:B13B12=MIN(IF(A$2:A$5=A12,F$2:F$5))
C12:C13C12=LOOKUP(2,1/((A$2:A$5=A12)*(F$2:F$5=B12)),B$2:B$5)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Solution

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,407
Office Version
  1. 365
Platform
  1. Windows
Something like this will work:

=MINIFS(F2:F5,A2:A5,"keyword")
=INDEX(B2:B5,MATCH(1,INDEX((A2:A5="keyword")*(F2:F5=MINIFS(F2:F5,A2:A5,"keyword")),0),0))

You can replace "keyword" with a cell ref and the minifs in 2nd formula with the previous result if you want.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,028
Office Version
  1. 365
Platform
  1. Windows
Another option, with dynamic functions
+Fluff 1.xlsm
ABCDEF
1QueryPageClicksImpressionsCTRPosition
2keywordCreate Your Free Website | Free Website Builder | Website.com6,833160,7254.25%4.2
3keywordhttps://www.website.com/subpage/6,23643,56314.31%2.1
4keyword twohttps://www.website.com/subpage/2,84424,97511.39%3.2
5keyword twohttps://www.website.com/subpage/subpage2,30320,95710.99%2.4
6
7
8
9
10
11QueryPositionPage
12keyword2.1https://www.website.com/subpage/
13keyword two2.4https://www.website.com/subpage/subpage
Summary
Cell Formulas
RangeFormula
B12:C13B12=INDEX(FILTER($B$2:$F$5,($A$2:$A$5=A12)*($F$2:$F$5=MIN(IF($A$2:$A$5=A12,$F$2:$F$5)))),1,{5,1})
Dynamic array formulas.
 

anthonyyo

New Member
Joined
Mar 30, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi,

Something like this would work.
Since you have 365, you can use MINIF rather then my CSE MIN/IF:
Thanks! This worked!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,421
Messages
5,642,018
Members
417,250
Latest member
spr1nger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top