Searchresult using 2 criterial

[Superfly3372]

I have been looking for hours on an answer and have tried multiple things. I just can't get it to work! I need Sheet1 B5 to result in 0.9268 (C6) from Sheet 2 (since Sheet2 A6 is 363 and B6 is 1) based on matching the criteria in cell Sheet 1 B2 (363) AND A5 (1).
Sheet1 A B
1 NPI 1255367611
2 DRG 363
3 Base $8,373.23
4 SOI Weight
5 1
6 2
7 3
8 4

Sheet 2 A B C D
1 APR DRG SOI SUBCLASS Relative Weight 31.0 Average Length of Stay
2 362 1 1.0393 2
3 362 2 1.3899 2
4 362 3 1.9466 5
5 362 4 4.8961 13
6 363 1 0.9268 2
7 363 2 1.5952 3
8 363 3 1.9454 5
9 363 4 4.3072 17

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX(Sheet2!$C$2:$C$9,MATCH($B6,IF(Sheet2!$A$2:$A$9=$B2,Sheet2!$B$2:$B$9),0))

 

[Superfly3372]

but if there is 363 in in B6, B7, B8 & B9 on Sheet 2 how can $B6 be part of the formula.

 

[Superfly3372]

Since 363 is in B6-9 on Sheet 2, I need it to pick the correct one based on the number in column B6-9 (1-4). It will not always be B6

 

[Aladin Akyurek]

but if there is 363 in in B6, B7, B8 & B9 on Sheet 2 how can $B6 be part of the formula.

Since 363 is in B6-9 on Sheet 2, I need it to pick the correct one based on the number in column B6-9 (1-4). It will not always be B6

Sheet2, A:D, the source data (or so it seems)

362	1	1.0393	2
362	2	1.3899	2
362	3	1.9466	5
362	4	4.8961	13
363	1	0.9268	2
363	2	1.5952	3
363	3	1.9454	5
363	4	4.3072	17

<TBODY>
</TBODY>

Sheet1, A:B, the destination (or so it seems)

NPI	1255367611
DRG	363
Base	$8,373.23
SOI	Weight
1
2
3
4

<TBODY>
</TBODY>

Are you not looking for a formula in B5, which must look up B2 and A5 and fetch a value from Sheet2?

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B	C
1	APR	DRG	SOI
2	362	1	1.0393
3	362	2	1.3899
4	362	3	1.9466
5	362	4	4.8961
6	363	1	0.9268
7	363	2	1.5952
8	363	3	1.9454
9	363	4	4.3072
Sheet2

Formula in B5 below copied down.

Excel Workbook
	A	B
1	NPI	1255367611
2	DRG	363
3	Base	$8,373.23*
4	SOI	Weight
5	1	0.9268
6	2	1.5952
7	3	1.9454
8	4	4.3072
Sheet1