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

 

[igold]

If your values in sheet OP maintain the same format, meaning that the upper value appears first and the lower value appears second in the list, then how about something like this. Since I did not know exactly where all your rows of data are, I used row 1. This should not be an issue because the formula is looking at the entire column as opposed to a range.

INDEX MATCH 3.xlsm
	H	I																Y	Z	AA
1		39800CE																277.05	291.22
2		39900CE																230	277.78
3
4
5		39900CE																230	277.78
6		40000CE																196.4	275.73
7
8
9
10		40000CE																196.4	275.73
11		40100CE																165	255.73
12
13
14		40100CE																165	255.73
15		40200CE																135	230.8
16
17
Pr

Cell Formulas
Range	Formula
Y1:Y15	Y1	=IFERROR(INDEX(OP!G:G,MATCH(I1,OP!F:F,0)+1),"")
Z1:Z15	Z1	=IFERROR(INDEX(OP!G:G,MATCH(I1,OP!F:F,0)),"")

 

[hsandeep]

@igold
Thanks for your help. Your formula works well.
1 exercise I would like to learn: your quote "I used row 1...", In Your above 2 formulas, only the formula in Y1 has "1" & there is no "1" in your Z1 formula. Why So?
IF I have to replicate your formula from row 1259, what & where the required change/(s) should I have to make in your 2 formulas?

 

[igold]

You're welcome, I was happy to help. Thanks for the feedback!

 

[hsandeep]

Can you please explain me the 'edited' comments of thread #3?

 

[hsandeep]

@igold
Will these 2 formulas wok properly or it may lead to some 'unknown' errors?
Y51

=MIN(OP!$G$1259:$G$1520*(OP!$F$1259:$F$1520=Pr!I51))

Z51=

MAX(OP!$G$1259:$G$1520*(OP!$F$1259:$F$1520=Pr!I51))

 

[igold]

The formula looks down the entire columns of OP (columns G and F) so you can use this formula anywhere as long as wherever you place the "Lookup Value" in the "Match" portion of the formula corresponds to the row in which the formula is placed (I hope that makes sense).

For use in Row 1259 your formulas would be:
Column Y: =IFERROR(INDEX(OP!G:G,MATCH(I1259,OP!F:F,0)+1),"")
Column Z: =IFERROR(INDEX(OP!G:G,MATCH(I1259,OP!F:F,0)),"")

The reason for the "+1" in the formula in Column Y is that the Index returns the first instance of the LookUp Value. Since there are two lookup values, I had to add 1 to the column to return the second instance.

 

[igold]

@igold
Will these 2 formulas wok properly or it may lead to some 'unknown' errors?
Y51

=MIN(OP!$G$1259:$G$1520*(OP!$F$1259:$F$1520=Pr!I51))

Z51=

MAX(OP!$G$1259:$G$1520*(OP!$F$1259:$F$1520=Pr!I51))

If it works, it works. There should not be any unknown errors in formulas...

 

[hsandeep]

For use in Row 1259 your formulas would be:
Column Y: =IFERROR(INDEX(OP!G:G,MATCH(I1259,OP!F:F,0)+1),"")
Column Z: =IFERROR(INDEX(OP!G:G,MATCH(I1259,OP!F:F,0)),"")

Thanks for your answer...I had replicated wrongly, now all good. Many thanks.

=IFERROR(INDEX(OP!G:G,MATCH(I1259,OP!F:F,0)+1),"")

=IFERROR(INDEX(OP!G:G,MATCH(I1259,OP!F:F,0)),"")

 

[igold]

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