I need some help with a better way to search the min/max value

[xROGERx]

Here´s the thing, I am an SEO Consultant and I am running a study where I compare the top 6 competitors from a client´s website, so many entries will be duplicates among the competitors and this is why I need help retrieving the min and max values from the Sheet2 into the Sheet1.

For example, the Sheet2 is the data from all 6 competitors but the data I am interested in are the ones shown in the Sheet1

Max Vol > This is the "Search Volume" metric int he Sheet2 (Col4)
Max Difficulty > This is the "Keyword Difficulty" metric in Sheet2 (Col5)
Min Position > This is the "Position" metric in Sheet2 (Col2)
URL > This is the "URL" metric in Sheet2 (Col7)

Sheet1

Book1.xlsx
	A	B	C	D	E	F
1	Keywords	Max Vol	Max Difficulty	Min Position	URL	Domain
2	summer season
3	valentines day
4	margarita recipe
5	orange
6	sabo skirt
7	espresso martini
8	click frenzy
9	click.frenzy
10	never have i ever
11	biscoff
12	margarita
13	espresso martini recipe
14	picnic basket
15	mr consistent
16	****tail
17	sangria
18	would you rather
19	watermelon sugar
20	amaretto sour
21	bloody mary
22	****tail glasses
23	clickfrenzy
24	mr
25	lime
26	consistent
27	picnic baskets
28	afterpay day
29	tumbler
Sheet1

Sheet2

Book1.xlsx
	A	B	C	D	E	F	G
1	Keyword	Position	Previous position	Search Volume	Keyword Difficulty	CPC	URL
2	summer season	14	14	201000	83	0,75	https://www.mrconsistent.com.au/products/summer-season-pack
3	valentines day	31	0	74000	85	0	https://www.mrconsistent.com.au/collections/valentines-day
4	margarita recipe	48	50	49500	68	0,16	https://www.mrconsistent.com.au/blogs/the-blog/margarita-in-30-seconds
5	orange	65	0	40500	82	0,28	https://www.mrconsistent.com.au/products/dried-orange-pack-40g
6	sabo skirt	13	11	40500	40	0,65	https://www.mrconsistent.com.au/blogs/the-blog/the-shake-up-podcast-founders-of-sabo-skirt
7	espresso martini	28	29	40500	68	0,1	https://www.mrconsistent.com.au/products/espresso-martini-10-serves
8	click frenzy	37	41	40500	65	0,59	https://www.mrconsistent.com.au/pages/click-frenzy
9	click.frenzy	37	37	40500	66	0,59	https://www.mrconsistent.com.au/pages/click-frenzy
10	never have i ever	39	0	33100	77	0,92	https://www.mrconsistent.com.au/products/the-shake-up-card-game-never-have-i-ever
11	biscoff	40	0	27100	47	1,08	https://www.mrconsistent.com.au/blogs/the-blog/did-someone-say-biscoff
12	margarita	28	31	27100	71	0,32	https://www.mrconsistent.com.au/products/margarita-bottle-10-serves
13	espresso martini recipe	20	22	18100	66	0,08	https://www.mrconsistent.com.au/products/espresso-martini-10-serves
14	picnic basket	19	19	18100	36	0,41	https://www.mrconsistent.com.au/products/sunsetbasket
15	mr consistent	1	1	18100	31	0,81	https://www.mrconsistent.com.au/
16	****tail	48	95	14800	80	0,68	https://www.mrconsistent.com.au/collections/single-mixers
17	sangria	27	0	12100	74	0,12	https://www.mrconsistent.com.au/products/sangria
18	would you rather	23	22	12100	69	0	https://www.mrconsistent.com.au/products/the-shake-up-card-game-would-you-rather
19	watermelon sugar	13	13	12100	59	0	https://www.mrconsistent.com.au/products/watermelon-sugar-****tail-rim-garnish
20	amaretto sour	41	46	12100	57	0,05	https://www.mrconsistent.com.au/blogs/the-blog/sours-in-30-seconds
21	bloody mary	29	32	12100	67	0,11	https://www.mrconsistent.com.au/products/bloody-mary-bottle-6-serves
22	****tail glasses	36	38	9900	50	0	https://www.mrconsistent.com.au/products/the-tumbler-****tail-glass
23	clickfrenzy	54	37	8100	64	0,59	https://www.mrconsistent.com.au/pages/click-frenzy
24	mr	18	18	8100	70	0	https://www.mrconsistent.com.au/collections/single-mixers
25	lime	23	21	8100	61	2,69	https://www.mrconsistent.com.au/products/dried-lime-pack
26	consistent	28	26	6600	69	0	https://www.mrconsistent.com.au/
27	picnic baskets	50	47	6600	31	0,41	https://www.mrconsistent.com.au/products/sunsetbasket
28	afterpay day	49	52	6600	41	1,29	https://www.mrconsistent.com.au/collections/afterpay-day
29	tumbler	48	48	6600	70	0,9	https://www.mrconsistent.com.au/products/the-tumbler-****tail-glass
Sheet2

Thoughts?

 

[BSALV]

i suppose i misunderstood the question ???

Test.xlsm
	A	B	C	D	E	F
1	Keywords	Max Vol	Max Difficulty	Min Position	URL	Domain
2	summer season	201000	83	14	https://www.mrconsistent.com.au/products/summer-season-pack	https://www.mrconsistent.com.au
3	valentines day	74000	85	31	https://www.mrconsistent.com.au/collections/valentines-day	https://www.mrconsistent.com.au
4	margarita recipe	49500	68	48	https://www.mrconsistent.com.au/blogs/the-blog/margarita-in-30-seconds	https://www.mrconsistent.com.au
5	orange	40500	82	65	https://www.mrconsistent.com.au/products/dried-orange-pack-40g	https://www.mrconsistent.com.au
6	sabo skirt	40500	40	13	https://www.mrconsistent.com.au/blogs/the-blog/the-shake-up-podcast-founders-of-sabo-skirt	https://www.mrconsistent.com.au
7	espresso martini	40500	68	28	https://www.mrconsistent.com.au/products/espresso-martini-10-serves	https://www.mrconsistent.com.au
8	click frenzy	40500	65	37	https://www.mrconsistent.com.au/pages/click-frenzy	https://www.mrconsistent.com.au
9	click.frenzy	40500	66	37	https://www.mrconsistent.com.au/pages/click-frenzy	https://www.mrconsistent.com.au
10	never have i ever	33100	77	39	https://www.mrconsistent.com.au/products/the-shake-up-card-game-never-have-i-ever	https://www.mrconsistent.com.au
11	biscoff	27100	47	40	https://www.mrconsistent.com.au/blogs/the-blog/did-someone-say-biscoff	https://www.mrconsistent.com.au
12	margarita	27100	71	28	https://www.mrconsistent.com.au/products/margarita-bottle-10-serves	https://www.mrconsistent.com.au
13	espresso martini recipe	18100	66	20	https://www.mrconsistent.com.au/products/espresso-martini-10-serves	https://www.mrconsistent.com.au
14	picnic basket	18100	36	19	https://www.mrconsistent.com.au/products/sunsetbasket	https://www.mrconsistent.com.au
15	mr consistent	18100	31	1	https://www.mrconsistent.com.au/	https://www.mrconsistent.com.au
16	****tail	14800	80	48	https://www.mrconsistent.com.au/collections/single-mixers	https://www.mrconsistent.com.au
17	sangria	12100	74	27	https://www.mrconsistent.com.au/products/sangria	https://www.mrconsistent.com.au
18	would you rather	12100	69	23	https://www.mrconsistent.com.au/products/the-shake-up-card-game-would-you-rather	https://www.mrconsistent.com.au
19	watermelon sugar	12100	59	13	https://www.mrconsistent.com.au/products/watermelon-sugar-****tail-rim-garnish	https://www.mrconsistent.com.au
20	amaretto sour	12100	57	41	https://www.mrconsistent.com.au/blogs/the-blog/sours-in-30-seconds	https://www.mrconsistent.com.au
21	bloody mary	12100	67	29	https://www.mrconsistent.com.au/products/bloody-mary-bottle-6-serves	https://www.mrconsistent.com.au
22	****tail glasses	9900	50	36	https://www.mrconsistent.com.au/products/the-tumbler-****tail-glass	https://www.mrconsistent.com.au
23	clickfrenzy	8100	64	54	https://www.mrconsistent.com.au/pages/click-frenzy	https://www.mrconsistent.com.au
24	mr	8100	70	18	https://www.mrconsistent.com.au/collections/single-mixers	https://www.mrconsistent.com.au
25	lime	8100	61	23	https://www.mrconsistent.com.au/products/dried-lime-pack	https://www.mrconsistent.com.au
26	consistent	6600	69	28	https://www.mrconsistent.com.au/	https://www.mrconsistent.com.au
27	picnic baskets	6600	31	50	https://www.mrconsistent.com.au/products/sunsetbasket	https://www.mrconsistent.com.au
28	afterpay day	6600	41	49	https://www.mrconsistent.com.au/collections/afterpay-day	https://www.mrconsistent.com.au
29	tumbler	6600	70	48	https://www.mrconsistent.com.au/products/the-tumbler-****tail-glass	https://www.mrconsistent.com.au
sheet1

Cell Formulas
Range	Formula
B2:B29	B2	=VLOOKUP($A2,sheet2!$A$1:$G$29,4,0)
C2:C29	C2	=VLOOKUP($A2,sheet2!$A$1:$G$29,5,0)
D2:D29	D2	=VLOOKUP($A2,sheet2!$A$1:$G$29,2,0)
E2:E29	E2	=VLOOKUP($A2,sheet2!$A$1:$G$29,7,0)
F2:F29	F2	=LEFT(E2,SEARCH("/",E2,15)-1)

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I need some help with a better way to search the min/max value
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.