# Thread: Need to understand Index/Match and Vlookup can be nested.

1. ## Need to understand Index/Match and Vlookup can be nested.

Hello,
Below is the final results of what I need: The Begin and Ending Balance for 3 Fund Numbers that have different codes.

I would key in the Fund Number and Code in Spreadsheet 1. I need a formula linked to a second spreadsheet named "Download" to apply the appropriate Beg/End Balance for each 4 Digit Code.

Here's the formula I used..which didn't work
=VLOOKUP(\$A19,MATCH(B19,' Download 4607'!\$B\$6:\$K\$664,0),6,FALSE)

Fund Number code Beg Bal End Balance
541705 2677 0.00 0.00
541705 2061 384,912.00 212,068.56
541705 2161 309,652.00 29,533.48

Download
Fund CODE Beg Bal End Balance
541705 2677 0.00 0.00
541705 2061 384,912.00 212,068.56
541705 2161 309,652.00 29,533.48

2. I realized I can combine the two columns (Fund + code) making it a unique value and then use VLookup.

3. Yes, you have to do it that way.

see attached example file.

The most efficient way is to use MATCH and INDEX rather than VLOOKUP.
Note that once you have matched the row number in the Download, you can use it to retrieve data from any column.
So it is used twice: onece to get the Beg Bal, and then again for the End Balance.

Using this method is far more efficient than using VLOOKUP for each value you want to return.

zeddy

4. ## The Following User Says Thank You to zeddy For This Useful Post:

ab2537 (2013-02-07)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•