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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0
You're welcome, thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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
Back
Top