# Looking up variable in table to generate corresponding value

#### monica_smith802

##### New Member
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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### James006

##### Well-known Member
Hi Monica,

Do you mean :

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

HTH

#### monica_smith802

##### New Member
Hi Monica,

Do you mean :

Code:
``=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

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Is this what you mean?

Excel Workbook
ABCDEFGHI
1FundA23v38s77\$500Value of InterestResult
2FundBs198e232\$534e2534
3FundC33tys244\$612
4
Lookup

#### monica_smith802

##### New Member
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>

 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

Replies
1
Views
137
Replies
3
Views
783
Replies
3
Views
356
Replies
3
Views
560
Replies
10
Views
652

1,195,837
Messages
6,011,884
Members
441,651
Latest member
drewe2000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back