MATCH/INDEX from 2 worksheets with a different criteria

[hsandeep]

2 ws are there
Ws1 “Pr” & ws2 “OP”

I want to MATCH I51:I65 of ws “Pr” with F1259:F1520 of ws “OP” & return ‘lower’ value of G (of ws “OP”) in Y (of ws “Pr) & return ‘upper’ value of G (of ws “OP”) in Z (of ws “Pr)
YOU MAY ADD HELPER COLUMNS/ROWS IF REQUIRED
How to accomplish?
Thanks in advance.

MATCH.xlsx
	I																Y	Z
51	39800CE																277.05	291.22
52	39900CE																230	277.78
53
54
55	39900CE																230	277.78
56	40000CE																196.4	275.73
57
58
59
60	40000CE																196.4	275.73
61	40100CE																165	255.73
62
63
64	40100CE																165	255.73
65	40200CE																135	230.8
Pr

MATCH.xlsx
	F	G
1259	39400CE	470.19
1260	39400CE	514.05
1261
1279	39500CE	431.95
1280	39500CE	444.35
1281
1299	39600CE	354.68
1300	39600CE	375.8
1301
1319	39700CE	320.03
1320	39700CE	332
1321
1339	39800CE	291.22
1340	39800CE	277.05
1341
1359	39900CE	277.78
1360	39900CE	230
1361
1379	40000CE	275.73
1380	40000CE	196.4
1381
1399	40100CE	255.73
1400	40100CE	165
1401
1419	40200CE	230.8
1420	40200CE	135
1421
1439	40300CE	228.13
1440	40300CE	113
1441
1459	40400CE	224.96
1460	40400CE	94.05
1461
1479	40500CE	198.45
1480	40500CE	74.55
1481
1499	40600CE	169.33
1500	40600CE	64.4
1501
1519	40700CE	130.78
1520	40700CE	56.6
OP

 

[Fluff]

@hsandeep in future please mark the the thread that contains the solution, not your post saying it works. Thanks

 

[hsandeep]

Great, I am glad you got it squared away. I hope my explanation was understandable.

If it is understandable, understandable

 

[hsandeep]

@hsandeep in future please mark the the thread that contains the solution, not your post saying it works. Thanks

Yes Sir...BTW when are you launching 'Excel based AI website solutions in competition with the ChatGPT?

 

[Fluff]

I'm not launching anything & ChatGPT is not that good.