Looking up variable in table to generate corresponding value

[monica_smith802]

Each fund has multiple sleeves and a Net Asset Value is provided for each fund. I am looking for a function that can search a table of sleeves to generate a corresponding value.

A B C D E F
1 FundA 23 v3 8s 77 $500
2 FundB s1 98 e2 32 $534
3 FundC 33 ty s2 44 $612


For example: If I search table B1:F3 for value e2 the formula will genarate $534 as an answer
**I believe this is done via INDEX/MATCH/COUNTIF


Thanks for your help!

 

[James006]

Hi Monica,

Do you mean :

=INDEX(F:F,MATCH("e2",D:D,0))

HTH

 

[monica_smith802]

Hi Monica,

Do you mean :

=INDEX(F:F,MATCH("e2",D:D,0))

HTH

No that function isn't working. I need something that can search the entire table not just one column. A similar formula I had for a different spreadsheet was

=INDEX(Sheet2!$X$2:$X$96,MIN(IF(Sheet2!$A$2:$W$96=Sheet1!A1,ROW(Sheet2!$A$2:$W$96)-ROW(Sheet2!$A$2)+1,"")))

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	FundA	23	v3	8s	77	$500		Value of Interest	Result
2	FundB	s1	98	e2	32	$534		e2	534
3	FundC	33	ty	s2	44	$612
4
Lookup

 

[monica_smith802]

Welcome to the MrExcel board!

Is this what you mean?

Lookup

*	A	B	C	D	E	F	G	H	I
1	FundA	23	v3	8s	77	$500	*	Value of Interest	Result
2	FundB	s1	98	e2	32	$534	*	e2	534
3	FundC	33	ty	s2	44	$612	*	*	*
4	*	*	*	*	*	*	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 118px;"><col style="width: 61px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula I2 =INDEX($F$1:$F$3,SUMPRODUCT(($B$1:$E$3=H2)*ROW($B$1:$E$3))-ROW($1:$1)+1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you!! This is exactly what I needed