Fill range with criteria

[hsandeep]

Range 1:B35:B49 contiguous rows & generates numerical values
Range2: D38:D49 non-contiguous rows & generates numerical values
Range 3: F35:F49 contiguous rows & generates numerical values

Output: H38:H49 should get corresponding F MATCHING D with B

How to accomplish please, thanks.

 

[Skybluekid]

Are you able to post some data and expected results?

 

[hsandeep]

Are you able to post some data and expected result

@Skybluekid
Here it is

MATCH.xlsx
	B	C	D	E	F	G	H
35	41600				11
36	41700				22
37	41800				33
38	41900		41800		44		33
39	42000		41900		55		44
40	42100		42000		66		55
41	42200				77
42	42300		42100		88		66
43	42400		42200		99		77
44	42500				111
45	42600		42300		222		88
46	42700		42400		333		99
47	42800				444
48	42900		42500		555		111
49	43000		42600		666		222
Sheet2

 

[awoohaw]

try this:

mr excel questions 25.xlsm
	A	B	C	D	E	F	G	H
1	41600				11
2	41700				22
3	41800				33
4	41900		41800		44		33	33
5	42000		41900		55		44	44
6	42100		42000		66		55	55
7	42200				77
8	42300		42100		88		66	66
9	42400		42200		99		77	77
10	42500				111
11	42600		42300		222		88	88
12	42700		42400		333		99	99
13	42800				444
14	42900		42500		555		111	111
15	43000		42600		666		222	222
hsandeep

Cell Formulas
Range	Formula
H4:H15	H4	=IFERROR(INDEX($E$1:$E$15,MATCH(C4,$A$1:$A$15)),"")

 

[hsandeep]

try this:

mr excel questions 25.xlsm
	A	B	C	D	E	F	G	H
1	41600				11
2	41700				22
3	41800				33
4	41900		41800		44		33	33
5	42000		41900		55		44	44
6	42100		42000		66		55	55
7	42200				77
8	42300		42100		88		66	66
9	42400		42200		99		77	77
10	42500				111
11	42600		42300		222		88	88
12	42700		42400		333		99	99
13	42800				444
14	42900		42500		555		111	111
15	43000		42600		666		222	222
hsandeep

Cell Formulas
Range	Formula
H4:H15	H4	=IFERROR(INDEX($E$1:$E$15,MATCH(C4,$A$1:$A$15)),"")

@awoohaw
Thanks, It is the formula needed. It works well. BTW will you recommend adding a 0 (zero in the formula)

=IFERROR(INDEX($E$1:$E$15,MATCH(C4,$A$1:$A$15,0)),"")

 

[hsandeep]

try this:

mr excel questions 25.xlsm
	A	B	C	D	E	F	G	H
1	41600				11
2	41700				22
3	41800				33
4	41900		41800		44		33	33
5	42000		41900		55		44	44
6	42100		42000		66		55	55
7	42200				77
8	42300		42100		88		66	66
9	42400		42200		99		77	77
10	42500				111
11	42600		42300		222		88	88
12	42700		42400		333		99	99
13	42800				444
14	42900		42500		555		111	111
15	43000		42600		666		222	222
hsandeep

Cell Formulas
Range	Formula
H4:H15	H4	=IFERROR(INDEX($E$1:$E$15,MATCH(C4,$A$1:$A$15)),"")

I am getting #N/A. Actually value of A1:A15 depends on B1:B15. Formula for A1:A15 shown. What to do?

MATCH.xlsx
	A	B	C	D	E	F	G	H
1	41600	BANKNIFTY23APR41600CE			11
2	41700	BANKNIFTY23APR41700CE			22
3	41800	BANKNIFTY23APR41800CE			33
4	41900	BANKNIFTY23APR41900CE	41800		44		33	#N/A
5	42000	BANKNIFTY23APR42000CE	41900		55		44	#N/A
6	42100	BANKNIFTY23APR42100CE	42000		66		55	#N/A
7	42200	BANKNIFTY23APR42200CE			77			#N/A
8	42300	BANKNIFTY23APR42300CE	42100		88		66	#N/A
9	42400	BANKNIFTY23APR42400CE	42200		99		77	#N/A
10	42500	BANKNIFTY23APR42500CE			111			#N/A
11	42600	BANKNIFTY23APR42600CE	42300		222		88	#N/A
12	42700	BANKNIFTY23APR42700CE	42400		333		99	#N/A
13	42800	BANKNIFTY23APR42800CE			444			#N/A
14	42900	BANKNIFTY23APR42900CE	42500		555		111	#N/A
15	43000	BANKNIFTY23APR43000CE	42600		666		222	#N/A
Sheet3

Cell Formulas
Range	Formula
A1:A15	A1	=MID(B1,LEN(B1)-6,5)
H4:H15	H4	=INDEX($E$1:$E$15,MATCH(C4,$A$1:$A$15))

 

[awoohaw]

Here you go. You MUST enter the formula with the CSE keystroke (CNTL-SHFT-ENTER), when you see the braces around the formula that is when you know you used the CSE keystroke to enter a formula. The double minus converts the text string to a number:

mr excel questions 25.xlsm
	A	B	C	D	E	F	G	H	I
1	41600	BANKNIFTY23APR41600CE			11
2	41700	BANKNIFTY23APR41700CE			22
3	41800	BANKNIFTY23APR41800CE			33
4	41900	BANKNIFTY23APR41900CE	41800		44		33	33	33
5	42000	BANKNIFTY23APR42000CE	41900		55		44	44	44
6	42100	BANKNIFTY23APR42100CE	42000		66		55	55	55
7	42200	BANKNIFTY23APR42200CE			77
8	42300	BANKNIFTY23APR42300CE	42100		88		66	66	66
9	42400	BANKNIFTY23APR42400CE	42200		99		77	77	77
10	42500	BANKNIFTY23APR42500CE			111
11	42600	BANKNIFTY23APR42600CE	42300		222		88	88	88
12	42700	BANKNIFTY23APR42700CE	42400		333		99	99	99
13	42800	BANKNIFTY23APR42800CE			444
14	42900	BANKNIFTY23APR42900CE	42500		555		111	111	111
15	43000	BANKNIFTY23APR43000CE	42600		666		222	222	222
hsandeep

Cell Formulas
Range	Formula
A1:A15	A1	=MID(B1,LEN(B1)-6,5)
I4:I15	I4	=IFERROR(INDEX($E$1:$E$15,MATCH(C4,--MID($B$1:$B$15,15,5),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hsandeep]

Here you go. You MUST enter the formula with the CSE keystroke (CNTL-SHFT-ENTER), when you see the braces around the formula that is when you know you used the CSE keystroke to enter a formula. The double minus converts the text string to a number:

mr excel questions 25.xlsm
	A	B	C	D	E	F	G	H	I
1	41600	BANKNIFTY23APR41600CE			11
2	41700	BANKNIFTY23APR41700CE			22
3	41800	BANKNIFTY23APR41800CE			33
4	41900	BANKNIFTY23APR41900CE	41800		44		33	33	33
5	42000	BANKNIFTY23APR42000CE	41900		55		44	44	44
6	42100	BANKNIFTY23APR42100CE	42000		66		55	55	55
7	42200	BANKNIFTY23APR42200CE			77
8	42300	BANKNIFTY23APR42300CE	42100		88		66	66	66
9	42400	BANKNIFTY23APR42400CE	42200		99		77	77	77
10	42500	BANKNIFTY23APR42500CE			111
11	42600	BANKNIFTY23APR42600CE	42300		222		88	88	88
12	42700	BANKNIFTY23APR42700CE	42400		333		99	99	99
13	42800	BANKNIFTY23APR42800CE			444
14	42900	BANKNIFTY23APR42900CE	42500		555		111	111	111
15	43000	BANKNIFTY23APR43000CE	42600		666		222	222	222
hsandeep

Cell Formulas
Range	Formula
A1:A15	A1	=MID(B1,LEN(B1)-6,5)
I4:I15	I4	=IFERROR(INDEX($E$1:$E$15,MATCH(C4,--MID($B$1:$B$15,15,5),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

@awoohaw
That was a cool cow!...haw! ...it really woo me...it works, thanks awoohaw

 

[awoohaw]

Happy to help. I'm glad the forum helped you find an answer.

Best wishes!